Use COUNTIF, ARRAYFORMULA and VLOOKUP in Microsoft Exel

0
votes

The first two columns in my Excel sheet are used as a VLOOKUP chart as follows:

-----------------
| 1  | Baseball |
-----------------
| 1  | Baseball |
-----------------
| 3  | Football |
-----------------
| 4  | Football |
-----------------

etc...

In Column C I have a list of numbers, such as 2,1,13.... In Column D I could do a VLOOKUP on each number to get me the sport that the number is associated with; however, I don't care about that. All I want to know is how many times each sport is represented at all, so in my short list Baseball is represented twice (2 and 1). With the VLOOKUP values I can then use a COUNTIF to count the number of times each sport shows up. In Google Sheets I can do all of this in one simple formula as follows: =COUNTIF(ARRAYFORMULA(VLOOKUP(C1:C100,A:B,2)),"Baseball") and I would get a number. ARRAYFORMULA doesn't exist in Excel though. Is there any way of doing this or do I need to make a hidden column for the intermediate step?

1
excelgoogle-sheets
Can't you just use COUNTIF against the numbers and forget the names? – NetMage
I want it to be expandable so that if in the VLOOKUP table you add another row for Baseball it would still work – Zachary Weixelbaum

1 Answers

1
votes

In Excel I would use SUMPRODUCT around a COUNTIFS():

=SUMPRODUCT(COUNTIFS(A:A,$C$1:$C$100,B:B,"Baseball"))

enter image description here