Count certain records between dates
 

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

Count certain records between dates
 

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


"Heliocracy" wrote:

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

Count certain records between dates
 

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



Teethless mama wrote:

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

"Heliocracy" wrote:

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

--

Dave Peterson

Count certain records between dates
 

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=D 1),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


"Heliocracy" wrote:

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

Count certain records between dates
 

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

"Dave Peterson" wrote:

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



Teethless mama wrote:

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

"Heliocracy" wrote:

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

--

Dave Peterson

Count certain records between dates
 

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.




Heliocracy wrote:

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

"Dave Peterson" wrote:

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



Teethless mama wrote:

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

"Heliocracy" wrote:

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

--

Dave Peterson


--

Dave Peterson

Count certain records between dates
 

You're a genius. Thanks.

"Dave Peterson" wrote:

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.




Heliocracy wrote:

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

"Dave Peterson" wrote:

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



Teethless mama wrote:

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

"Heliocracy" wrote:

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

--

Dave Peterson


--

Dave Peterson

Count certain records between dates
 

Glad it worked for you.

Heliocracy wrote:

You're a genius. Thanks.

"Dave Peterson" wrote:

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.




Heliocracy wrote:

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

"Dave Peterson" wrote:

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



Teethless mama wrote:

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

"Heliocracy" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson