Forum Discussion
Solved
IFERROR - HLOOKUP- IF formula error
Hello,
I am trying to use excel to build an attendance consolidation sheet. Each day has 7 hours (1 through 7).
The user has to fill in the absentee roll numbers (separated by comma) in the "Atte...
Detlef_Lewin wrote:
Jim,
first some preparations:
Remove the merged cells in "consolidated sheet" row 1 and type the weekday in every seven cells.
Sheet "sort" is not needed anymore.
This formula in "consolidated sheet" C3 and copy down and to the right:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&"!,")))*
Thank you for the reply Detlef.
I have done the corrections suggested by you. Now a #Name error shows up, anything related to the formula syntax?
Also will the row between Monday 7 and Tuesday 1 in the "Attendance entry" sheet matter?
File enclosed
Thank you for the help
Jim,
first some preparations:
Remove the merged cells in "consolidated sheet" row 1 and type the weekday in every seven cells.
Sheet "sort" is not needed anymore.
This formula in "consolidated sheet" C3 and copy down and to the right:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&"!,")))
*