But what if they are absent? SUMIFS

[Carvell]

OK I have a class of three students and a test of 4 questions. Questions 1 and 3 test skill A and questions 2 and 4 test skill B.

Skill A B A B
Question 1 2 3 4
Student A 1 1 1 1
Student B 0 0 0 0
Student C

I'm using SUMIFs and they work perfectly to show

So Student A, 2 marks for skill A and 2 marks for skill B
Student B, 0 marks for skill A and 0 marks for skill B

However student C was absent but the SUMIFS says they scored 0 and 0. I want it to report "Not tested yet" where there is a blank.

Thanks

 

[Joe4]

Can you post the SUMIFS formulas that you currently have, and we will try to help you amend them?

 

[Carvell]

=SUMIFS($J$7:M$7,$J$6:$M$6,S7)
S7 contains A

=SUMIFS($J$7:M$7,$J$6:$M$6,S8)
S8 contains B

Thank you

 

[Joe4]

Hmm...

I am having trouble matching up your data grid to your formulas.
What exactly is in J6:M6 and then J7:M7?

 

[Carvell]

Row 6 is the Skill so A or B (J6:M6)
Row 7 is the mark Student A got so 1 or 0 (J7:M7)
Row 8 is the mark Student B got so 1 or 0 (J8:M8)
Row 9 is the mark Student C got so 1 or 0 (J9:M9)
Thanks

 

[Joe4]

SUMIFS is always going to return a number.
If you want it to return a blank if all four columns in that row are blank, you can do so like this:
=IF(COUNTBLANK($J7:$M7)=4,"",SUMIFS($J7:$M7,$J$6:$M$6,$S$7))
=IF(COUNTBLANK($J7:$M7)=4,"",SUMIFS($J7:$M7,$J$6:$M$6,$S$8))

 

[Carvell]

Perfect!
Thank you

 

[Joe4]

You are welcome!