Countif for more than one criteria

  • Thread starter Thread starter Keyco
  • Start date Start date
K

Keyco

I am trying to use the countif function and need to count a person if they
are in a certain state and started before a certain month. I have the right
range and criteria for each of the two conditions but don't know how to use
them together. I tried =COUNTIF($D$7:$D$43,$B49,$U$7:$U$43,"<2"), but this
is not a valid formula. Any thoughts?
 
Use SUMPRODUCT:

=SUMPRODUCT(--($D$7:$D$43=$B49),--($U$7:$U$43<2))

Does that help?
Regards,
Paul
 
Paul,
Thanks for the help. Your formula worked but please help me to understand
why?

Keyco
 
Countif doesn't work cirectly with more than one criteria. SUMPRODUCT can
be used to get a count based on multiple criteria. It the method that I
suggested, the double minus (--) will convert each match, true or false, to
1s and 0s (ones and zeros). Then the results (true or false) are multiplied
together.

So if there is a criteria match in D7 and a match in U7, then results to
TRUE and TRUE, which converts to 1 and 1. (1 * 1 = 1)
If there is a criteria match in D8 and but no match in U8, then results to
TRUE and FALSE, which converts to 1 and 0. (1 * 0 = 0)
If there is no criteria match in D9 and no match in U9, then results to
FALSE and FALSE, which converts to 0 and 0. (0 * 0 = 0)
If there is no criteria match in D10 and but there is a match in U10, then
results to FALSE and TRUE, which converts to 0 and 1. (0 * 1 = 0)

These results are then totalled to get your count.
Another use for SUMPRODUCT would be to get the SUM of a column based on
multiple criteria. In that case, the SUM column would be added to the end
as follows:

=SUMPRODUCT(--($D$7:$D$43=$B49),--($U$7:$U$43<2),$Z$7:$Z$43)

Can also be written as:

=SUMPRODUCT(($D$7:$D$43=$B49)*($U$7:$U$43<2),$Z$7:$Z$43)

Regards,
Paul

--
 
Great explanation. Thanks for your help!

PCLIVE said:
Countif doesn't work cirectly with more than one criteria. SUMPRODUCT can
be used to get a count based on multiple criteria. It the method that I
suggested, the double minus (--) will convert each match, true or false, to
1s and 0s (ones and zeros). Then the results (true or false) are multiplied
together.

So if there is a criteria match in D7 and a match in U7, then results to
TRUE and TRUE, which converts to 1 and 1. (1 * 1 = 1)
If there is a criteria match in D8 and but no match in U8, then results to
TRUE and FALSE, which converts to 1 and 0. (1 * 0 = 0)
If there is no criteria match in D9 and no match in U9, then results to
FALSE and FALSE, which converts to 0 and 0. (0 * 0 = 0)
If there is no criteria match in D10 and but there is a match in U10, then
results to FALSE and TRUE, which converts to 0 and 1. (0 * 1 = 0)

These results are then totalled to get your count.
Another use for SUMPRODUCT would be to get the SUM of a column based on
multiple criteria. In that case, the SUM column would be added to the end
as follows:

=SUMPRODUCT(--($D$7:$D$43=$B49),--($U$7:$U$43<2),$Z$7:$Z$43)

Can also be written as:

=SUMPRODUCT(($D$7:$D$43=$B49)*($U$7:$U$43<2),$Z$7:$Z$43)

Regards,
Paul
Click to expand...
 
Back
Top