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)

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)

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)

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)