Sumproduct function
 

Hi,

In an attendance sheet, I need to calculate the number of fridays that each
employee has worked. Each cell captures either a text value (a combination of
X, M, S, A etc) or a numeral.

I'm trying to use the sumproduct function to do this:
Range E9:AI9 contains the calculated Fridays (using the weekday function)
Range E11: AI11 contains the attendance data

=sumproduct(--(E9:AI9="Fri"),(e11:ai110)

The formula gives the number of times "Fri" appears, but not the number of
times the relevant cell contains a numeric value.

Can you please help me??

Thanks

Neil,

It might be as simple as

=SUMPRODUCT(--(E9:AI9="Fri"),--(E11:AI110))

if row 9 really has text of Fri, not the actual dates. If real dates, try

=SUMPRODUCT(--(TEXT(E9:AI9,"ddd")="Fri"),--(E11:AI110))

but this means that only the numbers get counted, not X.M,S,A (which are
presumable absence codes).

If you want to total the amount then add ,E11:A11 into the formula.

--
HTH

Bob Phillips

"neil" wrote in message
...
Hi,

In an attendance sheet, I need to calculate the number of fridays that
each
employee has worked. Each cell captures either a text value (a combination
of
X, M, S, A etc) or a numeral.

I'm trying to use the sumproduct function to do this:
Range E9:AI9 contains the calculated Fridays (using the weekday function)
Range E11: AI11 contains the attendance data

=sumproduct(--(E9:AI9="Fri"),(e11:ai110)

The formula gives the number of times "Fri" appears, but not the number of
times the relevant cell contains a numeric value.

Can you please help me??

Thanks