Dear excelforum,
what i have is a workbook with 4 sheets namely summary, A, B, C. What i want to do i average each student achievement in term of P01 to P012?
Each sheet will contain different student and different PO Achievement. Not all the PO achievement will be available for each sheet, so i left them empty.
What i want to do is, for example, Joe will have on sheet A, PO3 =0.65 and Sheet B, P03=0.65. So the average would be (0.65+0.65)/2=0.65 .
At this point i have manage to make a sum of all the PO using =IFERROR((SUMPRODUCT(SUMIFS(INDIRECT("'"&$P$2:$P$4&"'!B2:B350"),INDIRECT("'"&$P$2:$P$4&"'!A2:A350"),A2))),0)
. However, i'm currently struggling to get the average since i couldn't get the sum of each PO for each student. It will be diff for each student. For example Joe will have two time of P03 but jack will only have 1 P03 throughout the three sheets. This is only 3 sheets. I have 400 names to add with 25 different sheet.
Any help appreciated? Here is the file. Calc1.xlsx
Bookmarks