COUNTIF based on DATE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date in one column and need to count the proper text in another column based on the month

For example

DATE TEX
1/1/04 LOS
1/2/04 DA
1/3/04 LOS
2/5/04 LOS

My solution would be 2 Lost for the month of January

Thanks. Am
 
Hi Amy

one option:
=SUMPRODUCT((MONTH(A2:A5)=1)*(B2:B5="Lost"))

Hope this helps

Cheers
JulieD
 
I learned this trick from the Great Frank Kabel (thanks Frank!

=SUMPRODUCT(--(MONTH(A14:A17)=1),--(B14:B17="lost")
This is tested and works, but doesn't check to see if the year is the same

=SUMPRODUCT(--(AND(MONTH(A14:A17)=1,YEAR(A14:A17)=2004)),--(B14:B17="lost")
This is tested and doesn't work (returns #VALUE). Anyone see what I'm missing here?
 
Hi

don't think you can use an AND in a SUMPRODUCT

try
=SUMPRODUCT(--(MONTH(A14:A17)=1),--(YEAR(A14:A17)=2004),--(B14:B17="lost"))

Cheers
Julie
Marcotte A said:
I learned this trick from the Great Frank Kabel (thanks Frank!)

=SUMPRODUCT(--(MONTH(A14:A17)=1),--(B14:B17="lost"))
This is tested and works, but doesn't check to see if the year is the same.
=SUMPRODUCT(--(AND(MONTH(A14:A17)=1,YEAR(A14:A17)=2004)),--(B14:B17="lost"))
This is tested and doesn't work (returns #VALUE). Anyone see what I'm
Click to expand...
missing here?
 
Try

=SUMPRODUCT(--(MONTH(A14:A17)=1),--(YEAR(A14:A17)=2004),--(B14:B17="lost"))


--(MONTH(A14:A17)=1),--(YEAR(A14:A17)=2004),--(B14:B17="lost"))

equals AND if it was possible to use it, same with this

=SUMPRODUCT((MONTH(A14:A17)=1)*(YEAR(A14:A17)=2004)*(B14:B17="lost"))

if you replace the * with + it will work as OR

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


Marcotte A said:
I learned this trick from the Great Frank Kabel (thanks Frank!)

=SUMPRODUCT(--(MONTH(A14:A17)=1),--(B14:B17="lost"))
This is tested and works, but doesn't check to see if the year is the same.
=SUMPRODUCT(--(AND(MONTH(A14:A17)=1,YEAR(A14:A17)=2004)),--(B14:B17="lost"))
This is tested and doesn't work (returns #VALUE). Anyone see what I'm
Click to expand...
missing here?
 
You wouldn't use AND in a statement like that, the * translates more or less
to AND, so just add another criteria in for year;

=SUMPRODUCT((MONTH(A14:A17)=1)*(YEAR(A14:A17)=2004)*(B14:B17="lost"))



Marcotte A said:
I learned this trick from the Great Frank Kabel (thanks Frank!)

=SUMPRODUCT(--(MONTH(A14:A17)=1),--(B14:B17="lost"))
This is tested and works, but doesn't check to see if the year is the same.
=SUMPRODUCT(--(AND(MONTH(A14:A17)=1,YEAR(A14:A17)=2004)),--(B14:B17="lost"))
This is tested and doesn't work (returns #VALUE). Anyone see what I'm
Click to expand...
missing here?
 
Cool, Thanks Julie, Peo and Dave. Learning more every day :

Peo - using + instead of * doesn't quite work exactly how I would expect. Using your formula and the OPs data, I get an answer of 10 (3 Januarys + 4 2004s + 3 "Lost"s). Do you know how you would get it to return 4? That is, count the number of records where Month=Jan OR Year=2004 or text="Lost"?
 
10 is correct, remember that you ask for month 1 OR 2004 OR lost

there are 3 lost, 3 month 1 and 4 2004, that totals 10.

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


Marcotte A said:
Cool, Thanks Julie, Peo and Dave. Learning more every day :)

Peo - using + instead of * doesn't quite work exactly how I would expect.
Click to expand...
Using your formula and the OPs data, I get an answer of 10 (3 Januarys + 4
2004s + 3 "Lost"s). Do you know how you would get it to return 4? That is,
count the number of records where Month=Jan OR Year=2004 or text="Lost"?
 
I think M. is wanting it to count a row when it has either of those, not
whether either of those show up in the entire range.

So the max of the count would be 4.
 
Back
Top