Tracking using =sumproduct

[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

I was given this formula to use and I can make it work if every thing
including the formula is on the same spred sheet but if I put this formula on
sheet 2 or 3 to pull the information from sheet 1 it will not work. Is it me
or is it the formula
Sorry for my ignorance

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

 

[Barb Reinhardt]

I'd add a date range like this

--(Sheet1!A1:A100 <= Date(2009,4,1))

That may be part of the problem.

 

[Alan]

'Sorry for my ignorance' Don't worry about that! I learned most of what
little I know from these newsgroups.

If you have the dates in say Sheet2, change the formula to

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

Sheet1!B1:B100 refers to B1:B100 on Sheet1
Sheet2!B1:B100 refers to B1:B100 on Sheet2

If you've changed Sheet1 to say 'Sickness', then instead of Sheet1!A1:A100,
in the formula use Sickness!A1:A100. Don't ask me why the ! is there, that's
just how it works.

Change the cell and sheet references to suit your needs and it will work
Regards,
Alan,

 

[grizzly6969]

Thank you works great
--
grizz

I'd add a date range like this

--(Sheet1!A1:A100 <= Date(2009,4,1))

That may be part of the problem.