Countif for more than one criteria

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

 

[PCLIVE]

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul

 

[Don Guillett]

try
=sumproduct((d7:d43=b49)*(u7:u43<2))

 

[Infinitogool]

Hi Keyco
Try
=sumproduct(--($D$7:$D$43=$B49),--($U$7:$U$43<2))

Regards,
pedro J.

 

[Keyco]

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

Keyco

 

[PCLIVE]

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

--

 

[Keyco]

Great explanation. Thanks for your help!

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