Excel Summary: Student Progress with Multiple Criteria. This guide demonstrates retrieving and summarizing student progress from a Source sheet to a Summary sheet based on different conditions.
Excel Summary: Student Progress with Multiple Criteria
Scenario:
I have two sheets: Source and Summary. The Source sheet contains student names, IDs, subjects, completion dates, and status.
I need a formula to check each student and subject in the Source sheet. If a completion date is missing, I want to display the status.
The formula in cell D2 is an array formula:
{=IFERROR(IF(INDEX(Source!$D$2:$D$12,MATCH(1,(Source!$A$2:$A$12=$B2)(Source!$C$2:$C$12=D$1),0))=0,INDEX(Source!$E$2:$E$12,MATCH(1,(Source!$A$2:$A$12=$B2)(Source!$C$2:$C$12=D$1),0)),INDEX(Source!$D$2:$D$12,MATCH(1,(Source!$A$2:$A$12=$B2)*(Source!$C$2:$C$12=D$1),0))),”Not Started”)} Remember to use CTRL + SHIFT + ENTER keys together for this formula.
Drag the formula down and across in the range D2:F6 to get statuses for all subjects:
To consolidate the status for each student in column G, use the formula =IF(AND(ISNUMBER(D25),ISNUMBER(E25),ISNUMBER(F25)),”Completed”,”Incomplete”)
This method efficiently summarizes student data based on information from another sheet.
