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

--

"Keyco" <(E-Mail Removed)> wrote in message
news:CAD5DB78-11D5-4A6C-94E6-(E-Mail Removed)...
>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?

try
=sumproduct((d7:d43=b49)*(u7:u43<2))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Keyco" <(E-Mail Removed)> wrote in message
news:CAD5DB78-11D5-4A6C-94E6-(E-Mail Removed)...
>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?

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?

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" <(E-Mail Removed)> wrote in message
> news:CAD5DB78-11D5-4A6C-94E6-(E-Mail Removed)...
> >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 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" <(E-Mail Removed)> wrote in message
news:5AB21309-A898-4120-934D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:CAD5DB78-11D5-4A6C-94E6-(E-Mail Removed)...
>> >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?
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:5AB21309-A898-4120-934D-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:CAD5DB78-11D5-4A6C-94E6-(E-Mail Removed)...
> >> >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?
> >>
> >>
> >>
>
>
>