sumproduct tracking

G

grizzly6969

I have a spred sheet tracking absenteeism using the following formula
=sumproduct(--('sheet1'!B1:B100="John Doe"),--('sheet1'!C1:C100="sick"))
each time John Doe is sick it displays on sheet 2
in column A is the dates - ------ I wish to track it Quarterly --- is it
possible to add a third criteria so it will track from (Jan. 01/09 to March
31/09) (April 01/09 to June 30/09 ) etc. I would prefer to use dates if
possible
 
A

Alan

One way, if you put the start and end dates in say N1 and N2.

=SUMPRODUCT(--(Sheet1!B1:B100="John
Doe"),--(Sheet1!C1:C100="sick"),--(A1:A100>=N1),--(A1:A100<=N2))

Regards,
Alan.
 
S

Sheeloo

Add two conditons... one to check > beginning of the period, second to check
< end of the period...

=SUMPRODUCT(--(Sheet1!B1:B100="John
Doe"),--(Sheet1!C1:C100="sick"),--(Sheet1!A1:A100>=$E$1),--(Sheet1!A1:A100<=$F$1))

with E1 having the beginning and F1 the end of the period
 
A

Alan

You must be, both Sheeoo's solution and mine work. Try posting the formula
you're using and the cells you've entered the start and end dates in, it
should be easily resolved.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tracking using =sumproduct 3
Help on Sumproduct 2
90 day tracking 1
Totalling number of text entries on a worksheet 3
LOOKUP function between two dates in Excel 2007 6
LOOKUP within a 1
Automatic Data Changes 1
rolling 3 month tracking 3

Top