countif help???

[scott]

I am trying to count how many times a customer has a discrepancy using a
COUNTIF formula.
I have set up:

=COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where
"CUSTOMER" can be selected.

However, I need to have counts per month. I tried to specify the data per
month like:

=IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates.

Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)?

Thanks for your time.
Scott

[daddylonglegs]

For more than one condition SUMPRODUCT can be used

=SUMPRODUCT(--(MONTH(B1:B100)=5),--(S1:S100="CUSTOMER"))

note you can't use whole columns as with COUNTIF

[Nikki]

for month of Dec you can use the following formula:

=SUMPRODUCT(--(S:S="Customer"),--(B:B="Dec"))
and so on....

"scott" wrote:

> I am trying to count how many times a customer has a discrepancy using a
> COUNTIF formula.
> I have set up:
>
> =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where
> "CUSTOMER" can be selected.
>
> However, I need to have counts per month. I tried to specify the data per
> month like:
>
> =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates.
>
> Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)?
>
> Thanks for your time.
> Scott

[Bob Phillips]

=SUMPRODUCT(--(S2:S200="CUSTOMER"),--(MONTH(B2:B200)=5))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"scott" <[email protected]> wrote in message
news:[email protected]...
> I am trying to count how many times a customer has a discrepancy using a
> COUNTIF formula.
> I have set up:
>
> =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where
> "CUSTOMER" can be selected.
>
> However, I need to have counts per month. I tried to specify the data per
> month like:
>
> =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates.
>
> Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)?
>
> Thanks for your time.
> Scott

[Nikki]

sorry just make sure you have a correct range in there,
=SUMPRODUCT(--(S1:S1000="Customer"),--(B1:B1000="Dec"))

"Nikki" wrote:

> for month of Dec you can use the following formula:
>
> =SUMPRODUCT(--(S:S="Customer"),--(B:B="Dec"))
> and so on....
>
> "scott" wrote:
>
> > I am trying to count how many times a customer has a discrepancy using a
> > COUNTIF formula.
> > I have set up:
> >
> > =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where
> > "CUSTOMER" can be selected.
> >
> > However, I need to have counts per month. I tried to specify the data per
> > month like:
> >
> > =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates.
> >
> > Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)?
> >
> > Thanks for your time.
> > Scott