Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions |