Countif for more than one criteria
 

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?

Countif for more than one criteria
 

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul

--

"Keyco" wrote in message
...
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?

Countif for more than one criteria
 

try
=sumproduct((d7:d43=b49)*(u7:u43<2))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Keyco" wrote in message
...
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?

Countif for more than one criteria
 

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

Regards,
pedro J.
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?

Countif for more than one criteria
 

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

Keyco


"PCLIVE" wrote:

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul

--

"Keyco" wrote in message
...
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?

Countif for more than one criteria
 

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" wrote in message
...
Paul,
Thanks for the help. Your formula worked but please help me to understand
why?

Keyco


"PCLIVE" wrote:

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul

--

"Keyco" wrote in message
...
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?

Countif for more than one criteria
 

Great explanation. Thanks for your help!

"PCLIVE" wrote:

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" wrote in message
...
Paul,
Thanks for the help. Your formula worked but please help me to understand
why?

Keyco


"PCLIVE" wrote:

Use SUMPRODUCT:

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

Does that help?
Regards,
Paul

--

"Keyco" wrote in message
...
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?