How do I count matches in Excel using an array of criteria?

0
votes

I've got a spreadsheet with several columns of data. Let's say they're names of animals. I also have a collection of terms I'd like to find in the data, and those terms are arranged in columns of related terms. So, for this example, a column of "Pets", with the terms "dog", "cat", "fish", each term appearing in a separate cell. Another set of terms could be "Food", with the terms "fish", "chicken", "turkey", etc.

Is there a way to cite an entire column of those terms--e.g., $E$2:$E$10--as the search criteria? I know there are ways to include multiple search criteria within combinations of SUM, SUMIFS, SUMPRODUCT, COUNT, COUNTIFS, COUNTA, MATCH, etc., but everything I've found so far would require me to name each term within an array formula.

2
excelcriteriacountif

2 Answers

0
votes

I would add some columns to help with this. For example, if you have the names of animals in column A and corresponding values in column B, pet terms in column E and food terms in column F, then you can add columns H and I with the following formulas:

=IF(ISERROR(VLOOKUP($A:$A,E:E,1,FALSE)),0,1)*$B:$B

and

=IF(ISERROR(VLOOKUP($A:$A,F:F,1,FALSE)),0,1)*$B:$B

Then just sum the values in columns H and I:

=sum(H:H)

and

=sum(I:I)
0
votes

Or something like the following where SUMPRODUCT is used to handle the arrays.

=SUMPRODUCT(COUNTIF(C1:C7,A1:A3))

Sumproduct countif