Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula
Hello,
I've got a formula which works perfectly to sum the number of days someone has been off sick in a rolling year, however I also need to count the number of occassions people have been off. Can anyone help me develop my existing formula or is there a totally diferent way to do it? I'm stuck. :( This is my current formula: =SUMPRODUCT(($S$1:$IV$1=TODAY()-365)*S2:IV2) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula
=COUNT($S$1:$IV$1)
"Jennie" wrote in message ... Hello, I've got a formula which works perfectly to sum the number of days someone has been off sick in a rolling year, however I also need to count the number of occassions people have been off. Can anyone help me develop my existing formula or is there a totally diferent way to do it? I'm stuck. :( This is my current formula: =SUMPRODUCT(($S$1:$IV$1=TODAY()-365)*S2:IV2) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula
BTW you do not need SUMPRODUCT
=SUMIF($S$1:$IV$1,"="&TODAY()-365,S2:IV2) "Jennie" wrote in message ... Hello, I've got a formula which works perfectly to sum the number of days someone has been off sick in a rolling year, however I also need to count the number of occassions people have been off. Can anyone help me develop my existing formula or is there a totally diferent way to do it? I'm stuck. :( This is my current formula: =SUMPRODUCT(($S$1:$IV$1=TODAY()-365)*S2:IV2) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT formula
Hi, thanks for this. The only problem is that I need the formula to only
count the cells within a rolling year. (The dates are in the row above) My initial formula: =SUMPRODUCT(($S$1:$IV$1=TODAY()-365)*S2:IV2) adds up all the figures correctly within the rolling 12 months, but I also need to count the cells (occasions) for the same condition. i.e if someone has been off sick for 4 weeks (20 days) I would merge the 4 cells together and only want excel to count this as 1 rather than 20. This is what I can't figure out. Is this possible? If necessary I can send you an example.... "Bob Phillips" wrote: =COUNT($S$1:$IV$1) "Jennie" wrote in message ... Hello, I've got a formula which works perfectly to sum the number of days someone has been off sick in a rolling year, however I also need to count the number of occassions people have been off. Can anyone help me develop my existing formula or is there a totally diferent way to do it? I'm stuck. :( This is my current formula: =SUMPRODUCT(($S$1:$IV$1=TODAY()-365)*S2:IV2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
sumproduct formula | Excel Discussion (Misc queries) | |||
OR in a SUMPRODUCT formula | Excel Worksheet Functions | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
sumproduct formula | Excel Worksheet Functions |