Can COUNTIF be nested to search two ranges

J

Jimbob

I need to use COUNTIF to search multiple ranges for combinations of answers

e.g Column A contains "colour", column B contains "shape"

I want to count how many blue circles there are etc...
 
D

Dave F

I would concatenate columna A and B and then countif the concatenated string

Example:
A1 = purple
B1 = square
C1 = CONCATENATE(A1,B1) --> yields purplesquare

Assume this goes to row 10

=COUNTIF(C1:C10,"purplesquare") gives you a count of purple squares

Dave
 
B

Bob Umlas, Excel MVP

I prefer "&" -- MUCH less to type, does same thing:
C1 has: =A1&B1
 
A

aidan.heritage

OR use the SUMPRODUCT function

=sumproduct(--(a1:a100="blue"),--(b1:b100="square))

would give you the total of blue squares - longer formula, but doesn't
need a new column to concatenate the result.
 
Top