Question

The ARRAY worksheet contains a list of the industries of the Fortune 500 companies. Use an array function to count the number of companies with A-level profitability (having an A in the Profitability column in the LOOKUP worksheet) for each industry. For example, only one company (Walt Disney) in the Media industry has A-level profitability, thus, the formula should return 1 for the Media industry in the ARRAY worksheet.

Array worksheet is below

	Number of companies with A-level profitability
Software & Services
Oil & Gas Operations
Insurance
Transportation
Utilities
Chemicals
Capital Goods
Technology Hardware & Equipment
Drugs & Biotechnology
Retailing
Aerospace & Defense
Banking
Media
Materials
Health Care Equipment & Services
Diversified Financials
Construction
Household & Personal Products
Conglomerates
Hotels, Restaurants & Leisure
Telecommunications Services
Food Drink & Tobacco
Semiconductors
Business Services & Supplies
Food Markets
Consumer Durables

CEO	Company	Industry	2008 Salary	2008 Bonus	Revenues	Profits	Margin	Profitability		Margin	Profitabilty Level
Lawrence Ellison	Oracle	Software & Services	1.00	10.78	17,996	4,274	0.24	A		-1	E
Ray Irani	Occidental Petroleum	Oil & Gas Operations	1.30	3.63	20,206	5,400	0.27	A		0	D
John Hess	Hess	Oil & Gas Operations	1.50	3.50	31,924	1,832	0.06	B		0.03	C
Mark Hurd	Hewlett-Packard	Technology Hardware & Equipment	1.45	8.93	104,286	7,264	0.07	B		0.05	B
John Hammergren	McKesson	Drugs & Biotechnology	1.47	3.86	101,703	990	0.01	D		0.1	A
Bradbury Anderson	Best Buy	Retailing	1.17	1.99	40,023	1,407	0.04	C
David O'Reilly	Chevron	Oil & Gas Operations	1.65	3.22	210,783	18,688	0.09	B
Frederick Smith	FedEx	Transportation	1.43	0.00	35,214	2,016	0.06	B
Robert Stevens	Lockheed Martin	Aerospace & Defense	1.77	4.25	41,862	3,033	0.07	B
Brian Roberts	Comcast	Media	2.77	8.28	30,895	2,587	0.08	B
John Rowe	Exelon	Utilities	1.47	1.84	18,925	2,736	0.14	A
Ronald Williams	Aetna	Health Care Equipment & Services	1.10	1.90	27,600	1,831	0.07	B
Chase Carey	DirecTV Group	Media	2.21	4.20	17,246	1,451	0.08	B
Thomas Ryan	CVS Caremark	Retailing	1.40	2.35	76,330	2,637	0.03	C
Miles White	Abbott Laboratories	Drugs & Biotechnology	1.80	4.20	25,914	3,606	0.14	A
Ivan Seidenberg	Verizon Communications	Telecommunications Services	2.10	3.74	93,775	5,521	0.06	B
Lloyd Blankfein	Goldman Sachs Group	Diversified Financials	0.60	0.00	87,968	11,599	0.13	A
William Swanson	Raytheon	Aerospace & Defense	1.23	3.05	22,426	2,578	0.11	A
Robert Iger	Walt Disney	Media	2.00	13.95	35,882	4,687	0.13	A
Richard Fairbank	Capital One Financial	Diversified Financials	0.00	0.00	18,966	1,570	0.08	B
Ronald Sargent	Staples	Retailing	1.11	0.62	19,373	996	0.05	B
Alan Boeckmann	Fluor	Construction	1.23	3.07	16,691	533	0.03	C
Bruce Smith	Tesoro	Oil & Gas Operations	1.30	0.00	21,675	566	0.03	D
William Weldon	Johnson & Johnson	Drugs & Biotechnology	1.79	3.70	61,095	10,576	0.17	A
George Paz	Express Scripts	Health Care Equipment & Services	0.94	2.45	18,378	568	0.03	C
Ronald Sugar	Northrop Grumman	Aerospace & Defense	1.51	3.09	32,032	1,790	0.06	B
Kenneth Chenault	American Express	Diversified Financials	1.25	0.00	32,316	4,012	0.12	A
W McNerney Jr	Boeing	Aerospace & Defense	1.92	1.48	66,387	4,074	0.06	B
David Farr	Emerson Electric	Conglomerates	1.20	3.00	22,572	2,136	0.09	B
Nicholas Chabraja	General Dynamics	Aerospace & Defense	1.38	4.50	27,294	2,072	0.08	B
John Faraci	International Paper	Materials	1.26	1.58	22,284	1,168	0.05	B
James Skinner	McDonald's	Hotels, Restaurants & Leisure	1.26	3.06	23,231	2,395	0.10	A
Jeffrey Bewkes	Time Warner	Media	1.75	7.60	46,615	4,387	0.09	B
Jeffrey Immelt	General Electric	Conglomerates	3.30	0.00	176,656	22,208	0.13	A
Jeffrey Noddle	Supervalu	Food Markets	1.13	1.30	44,048	593	0.01	D
David Wood	Murphy Oil	Oil & Gas Operations	0.67	0.63	18,439	766	0.04	C
James Owens	Caterpillar	Capital Goods	1.51	2.24	44,958	3,541	0.08	B
Stephen Roell	Johnson Controls	Consumer Durables	1.33	0.00	34,678	1,252	0.04	C
C Henrikson	MetLife	Insurance	1.00	3.25	53,150	4,317	0.08	B
James Mulva	ConocoPhillips	Oil & Gas Operations	1.50	1.42	178,558	11,891	0.07	B
Jeff Fettig	Whirlpool	Consumer Durables	1.26	0.42	19,451	640	0.03	C
H Hanway	Cigna	Health Care Equipment & Services	1.14	6.65	17,623	1,115	0.06	B
Mayo Shattuck III	Constellation Energy	Utilities	1.20	5.50	21,193	822	0.04	C
Michael Szymanczyk	Altria Group	Food Drink & Tobacco	1.20	2.80	38,051	9,786	0.26	A
William Klesse	Valero Energy	Oil & Gas Operations	1.50	0.71	96,758	5,234	0.05	B
Indra Nooyi	PepsiCo	Food Drink & Tobacco	1.30	2.60	39,474	5,658	0.14	A
Bernard Poussot	Wyeth	Drugs & Biotechnology	1.45	2.75	22,400	4,616	0.21	A
Ramani Ayer	Hartford Financial Services	Insurance	1.15	0.00	25,916	2,949	0.11	A
Anne Mulcahy	Xerox	Business Services & Supplies	1.32	0.99	17,228	1,135	0.07	B
Gerard Arpey	AMR	Transportation	0.66	0.00	22,935	504	0.02	D
Andrew Liveris	Dow Chemical	Chemicals	1.64	0.00	53,513	2,887	0.05	B
Glenn Tilton	UAL	Transportation	0.85	0.42	20,143	403	0.02	D
James Sinegal	Costco Wholesale	Retailing	0.35	0.08	64,400	1,083	0.02	D
Thomas Falk	Kimberly-Clark	Household & Personal Products	1.22	0.94	18,266	1,823	0.10	B
Clarence Cazalot Jr	Marathon Oil	Oil & Gas Operations	1.38	1.70	60,044	3,956	0.07	B
John Strangfeld Jr	Prudential Financial	Insurance	0.97	3.30	34,401	3,704	0.11	A
James Cornelius	Bristol-Myers Squibb	Drugs & Biotechnology	1.49	4.48	19,977	2,165	0.11	A
Michael Duke	Wal-Mart Stores	Retailing	0.98	4.46	378,799	12,731	0.03	C
R Yost	AmerisourceBergen	Drugs & Biotechnology	1.18	2.72	66,074	469	0.01	D
Alan Lafley	Procter & Gamble	Household & Personal Products	1.70	4.00	76,476	10,340	0.14	A
Randall Stephenson	AT&T	Telecommunications Services	1.42	0.00	118,928	11,951	0.10	A
Muhtar Kent	Coca-Cola	Food Drink & Tobacco	1.10	4.50	28,857	5,981	0.21	A
Louis Chenevert	United Technologies	Conglomerates	1.32	3.00	54,759	4,224	0.08	B
Paul Otellini	Intel	Semiconductors	1.00	3.87	38,334	6,976	0.18	A
Irene Rosenfeld	Kraft Foods	Food Drink & Tobacco	1.45	4.07	37,241	2,590	0.07	B
George Buckley	3M	Conglomerates	1.72	2.64	24,462	4,096	0.17	A
Daniel Hesse	Sprint Nextel	Telecommunications Services	1.20	2.65	40,146	(29,580)	-0.74	E
David Dillon	Kroger	Food Markets	1.17	2.32	70,235	1,180	0.02	D
Stephen Hemsley	UnitedHealth Group	Health Care Equipment & Services	1.30	2.54	75,431	4,654	0.06	B
Jeffrey Kindler	Pfizer	Drugs & Biotechnology	1.58	3.00	48,418	8,144	0.17	A
R Clark	Cardinal Health	Drugs & Biotechnology	1.44	0.69	88,364	1,931	0.02	D
Mary Sammons	Rite Aid	Retailing	1.00	1.82	24,418	(1,079)	-0.04	E
Klaus Kleinfeld	Alcoa	Materials	1.40	1.88	30,748	2,564	0.08	B
Jeffrey Joerres	Manpower	Business Services & Supplies	1.00	0.30	20,500	485	0.02	D
Angela Braly	WellPoint	Health Care Equipment & Services	1.14	0.07	61,134	3,345	0.05	B
Alan Mulally	Ford Motor	Consumer Durables	2.00	0.00	172,468	(2,723)	-0.02	E
Gregory Spierkel	Ingram Micro	Technology Hardware & Equipment	0.80	0.84	35,047	276	0.01	D
James Tisch	Loews	Insurance	1.10	2.50	17,920	2,489	0.14	A
Gregory Brown	Motorola	Technology Hardware & Equipment	1.20	0.00	36,622	(49)	0.00	E
Terry Lundgren	Macy's	Retailing	1.50	0.90	26,340	893	0.03	C
John Chambers	Cisco Systems	Technology Hardware & Equipment	0.38	3.00	34,922	7,333	0.21	A
William DeLaney	Sysco	Food Markets	0.56	1.94	35,042	1,001	0.03	D
Robert Dutkowsky	Tech Data	Technology Hardware & Equipment	0.92	1.59	23,423	108	0.00	D
Francis Blake	Home Depot	Retailing	1.01	0.00	84,740	4,395	0.05	B
Vikram Pandit	Citigroup	Banking	0.96	0.00	159,229	3,617	0.02	D
Thomas Wilson	Allstate	Insurance	1.04	0.15	36,769	4,636	0.13	A
Steven Burd	Safeway	Food Markets	1.47	0.40	42,286	888	0.02	D
Gregg Steinhafel	Target	Retailing	1.35	0.45	63,367	2,849	0.04	C
Michael McCallister	Humana	Health Care Equipment & Services	1.02	0.00	25,290	834	0.03	C
Frederick Henderson	General Motors	Consumer Durables	1.72	0.00	182,347	(38,732)	-0.21	E
Lynn Elsenhans	Sunoco	Oil & Gas Operations	1.24	0.79	42,101	891	0.02	D
Michael Dell	Dell	Technology Hardware & Equipment	0.95	0.00	61,133	2,947	0.05	C
Gregory Wasson	Walgreen	Retailing	0.75	0.65	53,762	2,041	0.04	C
D Davis	United Parcel Service	Transportation	0.96	0.18	49,692	382	0.01	D
John Brock	Coca-Cola Enterprises	Food Drink & Tobacco	1.14	0.15	20,936	711	0.03	C
John Stumpf	Wells Fargo	Banking	0.88	0.00	53,593	8,057	0.15	A
Steven Ballmer	Microsoft	Software & Services	0.64	0.70	51,122	14,065	0.28	A
Michael Jackson	AutoNation	Retailing	1.15	0.00	17,951	279	0.02	D
Rodney O'Neal	Delphi	Consumer Durables	1.20	0.00	26,160	(3,065)	-0.12	E
John Mack	Morgan Stanley	Diversified Financials	0.80	0.00	87,879	3,209	0.04	C
W Johnson	Sears Holdings	Retailing	0.90	0.00	50,703	826	0.02	D
Daniel Fulton	Weyerhaeuser	Materials	0.79	0.00	16,871	790	0.05	C
Richard Anderson	Delta Air Lines	Transportation	0.60	0.00	19,154	1,612	0.08	B
Leland Tollett	Tyson Foods	Food Drink & Tobacco	0.13	0.00	26,900	268	0.01	D
Warren Buffett	Berkshire Hathaway	Diversified Financials	0.10	0.00	118,245	13,213	0.11	A
Herbert Allison Jr	Fannie Mae	Diversified Financials	0.00	0.00	43,355	(2,050)	-0.05	E
Steven Jobs	Apple	Technology Hardware & Equipment	0.00	0.00	24,006	3,496	0.15	A
John Koskinen	Freddie Mac	Diversified Financials	0.00	0.00	43,104	(3,094)	-0.07	E

Answer 1

Number of companies with A-level profitability	Count
Software & Services	2
Oil & Gas Operations	1
Insurance	4
Utilities	1
Technology Hardware & Equipment	2
Drugs & Biotechnology	5
Aerospace & Defense	1
Banking	1
Media	1
Diversified Financials	3
Household & Personal Products	1
Conglomerates	2
Hotels, Restaurants & Leisure	1
Telecommunications Services	1
Food Drink & Tobacco	3
Semiconductors	1
TOTAL	30

Name first tab of the worksheet as "Array worksheet" and second tab of the worksheet as " LOOKUP worksheet" and put the below formula against each cell of Industry in tab "Array worksheet".

Formula : =COUNTIFS(' LOOKUP worksheet'!C:C,'Array worksheet'!A2,' LOOKUP worksheet'!I:I,"A")

Paste the above formula against each cell of Industry in tab "Array worksheet" and you will get the count the number of companies with A-level profitability. Filter it to more than zero "0" and you will get the total count the number of companies with A-level profitability. Total count is 30.

Thanks.