Can COUNTIF be nested to search two ranges

[Guest]

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...

 

[Guest]

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

 

[Guest]

Thanks Dave. I'd never heard of the concatenate function.

 

[Guest]

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

 

[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.