counting the occurrence of specific text within a date range
 

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

counting the occurrence of specific text within a date range
 

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

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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.
--
John

counting the occurrence of specific text within a date range
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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.
--
John


.

counting the occurrence of specific text within a date range
 

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_ra nge,"<="&C1)

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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.
--
John


.

counting the occurrence of specific text within a date range
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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


John wrote:

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" wrote:

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

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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.
--
John


.


--

Dave Peterson