counting the occurrence of specific text within a date range

[John]

In a sheet I have activities completed by specific staff on specific date. I
want to countthe number of times the activity occurs within each month (a
date range i.e. 05/01/10 through 5/31/10). For each instance that the
specific staff memebr completes the activity their name appears adjacent to
the date the activity was completed. The dates are not in chronological
order, thus the need to be able to search and find each date within the date
range and to count how many times the specific staff member's name appears
within that date range.

 

[T. Valko]

Try this...

Use cells to hold the criteria.

A1 = name to count for
B1 = lower date boundary
C1 = upper date boundary

Then:

=SUMPRODUCT(--(name_range=A1),--(date_range>=B1),--(date_range<=C1))

 

[John]

Is there a way to use the countif function to do this. at risk of seemingto
be as stupid as I am he's what I'V tried but get an error--
If(ao5:ao515,<01/01/10:01/31/10>),COUNTIF(AP5:AP515,"Abbott")
John

 

[T. Valko]

No, you can't use COUNTIF to do this. However, if you're using Excel 2007
(or later) you can use COUNTIFS (which is a more efficient, somewhat limited
version of SUMPRODUCT).

=COUNTIFS(name_range,A1,date_range,">="&B1,date_range,"<="&C1)

 

[Dave Peterson]

Starting with Biff's suggestion:

=SUMPRODUCT(--(name_range=A1),--(date_range>=B1),--(date_range<=C1))

=sumproduct(--(ao5:ao515>=date(2010,1,1)),
--(ao5:ao515<=date(2010,1,31)),
--(ap5:ap515="abbott"))

or since you're looking at a single month:

=sumproduct(--(text(ao5:ao515,"yyyymm")="201001"),
--(ap5:ap515="abbott"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html