How to use range in Excel formula criteria

Question

I have requirement where I have range for which I want to perform count. Because there is a condition, I want to use COUNTIF/COUNTIFS function. In criteria, I want to use range as I have multiple value that can be matched. Following is an example

Values to be counted - APPLE, MANGO, ORANGE, MANGO, APPLE, APPLE, ORANGE, ORANGE

What I want is count for APPLE and ORANGE for example but I want to keep it generelized. That means in another column I want to put values for which I want count. So at a time I can put only APPLE, at another time APPLE, MANGO and so on. If I'm still not clear enough then in another words, I want a count of values which are determined by the values in another range.

I could not find any way to use range in the 'criteria' parameter of the COUNTIF/COUNTIFS function. I know I can use sum of multiple COUNTIF with different value in each 'criteria' but that becomes hardcoded and everytime I need to change the formula.

Can someone help with how can I use a range (instead of single value) in 'criteria' ?

Answer 1

perhaps

=SUMPRODUCT(COUNTIF(data_range,criteria_range))

Answer 2

Let's assume your data list is in column A and your "values to be counted" occupy, say, D1:D5.

In cell B1, use =IF(ISNA(VLOOKUP(A1,$D$1:$D$5,1,FALSE)),0,1). This will write 1 if A1 appears in D1:D5, 0 otherwise.

Copy this formula downwards. Note that it will return 0 for a blank cell so you can extend this formula as far as you like thereby future-proofing your sheet.

In another cell, sum column B, =SUM(B1:B...) Where B... is the end of the formula range.

I don't think you can arrive at the final formula in one step using arrays.

Answer 3

I suggest you investigate the Excel formula DCOUNT

http://office.microsoft.com/en-us/excel-help/dcount-HP005209049.aspx

Microsoft even use a fruit based example for you !

Answer 4

CountIf accepts a range, and a criteria. The criteria can reference a range.

For example:

=CountIf(A:A,"Apple") counts the number of occurrences of the word "Apple" in column A.

And:

=CountIf(A:A,B2) counts the number of occurrences of *whatever value is in Cell B2, in column A.

If you use two cells to define the search criteria, say, B2 and C2, you could do:

=CountIf(A:A,B2)+CountIf(A:A,C2)

Or using CountIfs:

=CountIfs(A:A,B2,A:A,C2)

If you must put both search criteria in a single cell (seems like a problem of bad worksheet structure, rather than any good design requirement), then you can modify the criteria of any of the above functions using string functions:

Ex: Say your criteria of Apple, Orange is a single cell, C2, delimited by the comma:

=CountIf(A:A,Trim(MID(C2,1,FIND(",",C2)-1))) + CountIf(A:A,TRIM(MID(C2,FIND(",",C2)+1,LEN(C2)))

I still think you're better served using a proper CountIfs or summing two CountIf with indpendent criteria ranges, rather trying to troubleshoot errors, or build complex formula (i.e., modify the above to work even if there is no comma, or a single value, etc....)