Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking using =sumproduct
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)) -- grizz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking using =sumproduct
I'd add a date range like this
--(Sheet1!A1:A100 <= Date(2009,4,1)) That may be part of the problem. "grizzly6969" wrote: 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)) -- grizz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking using =sumproduct
'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" wrote in message ... 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)) -- grizz |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking using =sumproduct
Thank you works great
-- grizz "Barb Reinhardt" wrote: I'd add a date range like this --(Sheet1!A1:A100 <= Date(2009,4,1)) That may be part of the problem. "grizzly6969" wrote: 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)) -- grizz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct tracking | Excel Discussion (Misc queries) | |||
Tracking rks | Excel Discussion (Misc queries) | |||
sumproduct function, Bob P please see tracking sheet help | New Users to Excel | |||
How to insert tracking numbers into my webpage for RMA tracking | Excel Discussion (Misc queries) | |||
Tracking Changes | Excel Discussion (Misc queries) |