COUNTIF- add criteria

  • Thread starter Thread starter lunker55
  • Start date Start date
L

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

Bernard Liengme said:
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 said:
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
Click to expand...
Click to expand...
 
try removing it.

--
Don Guillett
SalesAid Software
(e-mail address removed)
lunker55 said:
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

Bernard Liengme said:
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 said:
I have a COUNTIF function:
=0+COUNTIF('[Service Totals All
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top