Sum If puzzle

[Jazz M]

Hi folks, I am running a marking centre this weekend and I wish to sum track 'live marking' of exams over the course of the day.
I have 3 teams, each correcting one exam (Exam A, Exam B, Exam C). However I only wish to see a candidates overall result once a score has been posted to each of the Exam cells.

Can you help me write a formula that will only sum the 3 cells if there is a score present and not perform the calculation if there is not a score in all 3 cells ?

The example below in the first instance there is no score for Exam C so i dont wish for an overall result to be totalled. However in the second instance there is a score recorded in all 3 exams so i wish for the overall result to be recorded.

there are thousands of candidates so hence looking for a formula.

thanks as always in advance folks.

Jazz M Exam A Exam B Exam C Overall Result 25% 15% 15% 17% 9% 41% <tbody> </tbody>

<tbody>
</tbody>

 

[JazzSP8]

Hi

You could use something like;

=IF(COUNT(A2:C2)<>3,"",SUM(A2:C2))

That should do it

 

[Jazz M]

Why Thank you JazzSP8

That has worked perfectly.

really grateful
cheers
Jazz M

Hi

You could use something like;

=IF(COUNT(A2:C2)<>3,"",SUM(A2:C2))

That should do it

 

[Jazz M]

Good morning

the formula worked well for me where cells that were not corrected were blank.

however I came across instances where an exam cell was populated with a 0 instead of a blank. See the first row below for an example.

Is there a way to modify the formula so that it does not sum the 3 cells if a 0 value appears ?

Exam A	Exam B	Exam C	Overall Result
25%	15%	0%
15%	17%	9%	41%

<tbody>
</tbody>

thanks in advance.
Jazz

 

[Rick Rothstein]

Give this formula a try...

=IF(COUNTIF(A2:C2,">0")=3,SUM(A2:C2),"")

 

[Jazz M]

Give this formula a try...

=IF(COUNTIF(A2:C2,">0")=3,SUM(A2:C2),"")

Thanks Rick, that solved it.

Kind regards,
Jazz