Countif for more than one criteria

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

PCLIVE

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul
 
I

Infinitogool

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

Regards,
pedro J.
 
K

Keyco

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

Keyco
 
P

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

--
 
K

Keyco

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top