COUNTIF- add criteria

[lunker55]

I have a COUNTIF function:
=0+COUNTIF('[Service Totals All Months.xls]2003-2004'!$L$4:$L$2000,"Dave H")

I also want to add a criteria:
COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

My "A" column is full of dates (days, months, years) formatted properly.

I don't know how to insert the MONTH function properly.

=0+COUNTIF('[Service Totals All Months.xls]2003-2004'!$L$4:$L$2000,"Dave
H")-COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

I think it should be like this, but (month=3) instead of looking for the
text "March"

Am I close?

Joe

 

[Don Guillett]

try
=sumproduct(rngA="Dave H")*(month(rngB)=3))

 

[Bernard Liengme]

Hi Joe,
When you have more that one criterion it time to dump COUNTIF and use
SUMPRODUCT. Try this (I suggest you get it working on the Service Total file
first):
=SUMPRODUCT(--(L4:L2000="Dave H"), (-- MONTH(A4:A2000)=3))
Yes, that is two negation in a row: it coerces the TRUE/FALSE of the Boolean
expression into 1s and 0s.

SUMPRODUCT (and COUNTIF I seem to recall) need the referenced file to be
open.

By the way why do your formulas start with =0+....?

Best wishes
Bernard

 

[lunker55]

Thanks Don and Bernard.

Bernard, I don't know why there is a "0+" at the beginning. I thought
someone told me to do it for a reason, but I can't remember.
I am afraid to remove it just incase!

Joe

Hi Joe,
When you have more that one criterion it time to dump COUNTIF and use
SUMPRODUCT. Try this (I suggest you get it working on the Service Total file
first):
=SUMPRODUCT(--(L4:L2000="Dave H"), (-- MONTH(A4:A2000)=3))
Yes, that is two negation in a row: it coerces the TRUE/FALSE of the Boolean
expression into 1s and 0s.

SUMPRODUCT (and COUNTIF I seem to recall) need the referenced file to be
open.

By the way why do your formulas start with =0+....?

Best wishes
Bernard

I have a COUNTIF function:
=0+COUNTIF('[Service Totals All Months.xls]2003-2004'!$L$4:$L$2000,"Dave H")

I also want to add a criteria:
COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

My "A" column is full of dates (days, months, years) formatted properly.

I don't know how to insert the MONTH function properly.

=0+COUNTIF('[Service Totals All Months.xls]2003-2004'!$L$4:$L$2000,"Dave
H")-COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

I think it should be like this, but (month=3) instead of looking for the
text "March"

Am I close?

Joe

 

[Don Guillett]

try removing it.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks Don and Bernard.

Bernard, I don't know why there is a "0+" at the beginning. I thought
someone told me to do it for a reason, but I can't remember.
I am afraid to remove it just incase!

Joe

Hi Joe,
When you have more that one criterion it time to dump COUNTIF and use
SUMPRODUCT. Try this (I suggest you get it working on the Service Total file
first):
=SUMPRODUCT(--(L4:L2000="Dave H"), (-- MONTH(A4:A2000)=3))
Yes, that is two negation in a row: it coerces the TRUE/FALSE of the Boolean
expression into 1s and 0s.

SUMPRODUCT (and COUNTIF I seem to recall) need the referenced file to be
open.

By the way why do your formulas start with =0+....?

Best wishes
Bernard

I have a COUNTIF function:
=0+COUNTIF('[Service Totals All

Months.xls]2003-2004'!$L$4:$L$2000,"Dave
H")

I also want to add a criteria:
COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

My "A" column is full of dates (days, months, years) formatted properly.

I don't know how to insert the MONTH function properly.

=0+COUNTIF('[Service Totals All Months.xls]2003-2004'!$L$4:$L$2000,"Dave
H")-COUNTIF('[Service Totals All Months.xls]2003-2004'!$A$4:$A$2000,"March")

I think it should be like this, but (month=3) instead of looking for the
text "March"

Am I close?

Joe