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.

[TABLE="class: cms_table, width: 326"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jazz M

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]Exam A[/TD]
[TD]Exam B[/TD]
[TD]Exam C[/TD]
[TD]Overall Result[/TD]
[/TR]
[TR]
[TD]25%[/TD]
[TD]15%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15%[/TD]
[TD]17%[/TD]
[TD]9%[/TD]
[TD]41%[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ?

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Exam A[/TD]
[TD="class: xl63, width: 64"]Exam B[/TD]
[TD="class: xl63, width: 64"]Exam C[/TD]
[TD="class: xl63, width: 64"]Overall Result[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]25%[/TD]
[TD="class: xl64, width: 64, align: right"]15%[/TD]
[TD="class: xl64, width: 64, align: right"]0%[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, align: right"]15%[/TD]
[TD="class: xl64, width: 64, align: right"]17%[/TD]
[TD="class: xl64, width: 64, align: right"]9%[/TD]
[TD="class: xl64, width: 64, align: right"]41%[/TD]
[/TR]
</tbody>[/TABLE]

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