Can COUNTIF be nested to search two ranges
 

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

Can COUNTIF be nested to search two ranges
 

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
--
Brevity is the soul of wit.


"Jimbob" wrote:

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

Can COUNTIF be nested to search two ranges
 

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

"Dave F" wrote:

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
--
Brevity is the soul of wit.


"Jimbob" wrote:

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

Can COUNTIF be nested to search two ranges
 

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

"Jimbob" wrote:

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

"Dave F" wrote:

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
--
Brevity is the soul of wit.


"Jimbob" wrote:

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

Can COUNTIF be nested to search two ranges
 

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.

Dave F wrote:

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
--
Brevity is the soul of wit.


"Jimbob" wrote:

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