Count certain records between dates

H

Heliocracy

For each record, Column B is a date and Column O is either "Maintenance" or
"Routine." How do I count the number of "Maintenance" records whose dates
fall within a specific month--ex: count number of records that are dated
anytime in October 2006 (in column B) AND have the word "Maintenance" (in
column O)?
 
T

Teethless mama

=SUMPRODUCT(--(TEXT(A2:A100,"mmmm yyyy")="Octobober
2006"),--(O2:O100="Maintenance"))
 
D

Dave Peterson

It may be easier to use the month number (mm) instead of month name (mmmm).
It'll be easier to avoid typos <vbg>.

=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),--(O2:O100="Maintenance"))
 
R

Ron Coderre

Try something like this:

With
A2:A100 containing dates (or blanks)
O2:O100 containing associated categories (including some "Maintenance")

C1: (a period reference in the form YYYYMM....eg 200710 for October 2007)
D1: (a category to find.....eg Maintenance)

This formula returns the count of Maintenance items in October 2007
E1: =INDEX(FREQUENCY(TEXT(A2:A100,"yyyymm")*(O2:O100=D1),C1-1),2)

Notes:
The FREQUENCY function will return 2 values in an array.
The first one is the count of non-matching items
The second one is the count of matching items.
The INDEX function returns that second value

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
H

Heliocracy

Thanks to you both, this appears to produce the correct answer. Why can't I
use a wildcard in the "Maintenance" to allow for misspelling or spaces after
the end of the word? Thanks
 
D

Dave Peterson

Because excel won't let you <bg>.

But you could stop looking where you want:

=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),--(left(O2:O100,5)="Maint"))

or if "maint" could be anywhere in that cell:
=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),
--(ISNUMBER(SEARCH("maintenance",O2:O100))))

=search() doesn't care about case.
=Find() is case sensitive.
 
H

Heliocracy

You're a genius. Thanks.

Dave Peterson said:
Because excel won't let you <bg>.

But you could stop looking where you want:

=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),--(left(O2:O100,5)="Maint"))

or if "maint" could be anywhere in that cell:
=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200610"),
--(ISNUMBER(SEARCH("maintenance",O2:O100))))

=search() doesn't care about case.
=Find() is case sensitive.
Click to expand...
 
Top