Formula that has sum and exception

[Excelnovice13]

Back with another question please

In example below how do I create a formula for row 9 ( total of all) that does the following
Sums row 4 and 8 and if both are numbers gives that values but if either is zero the total of all is zero



Row 1 — 2
Row 2– 1
Row 3—1
Row 4 Total. 4
Row 5 — auto fail
Row 6—3
Row 7– 1
Row 8 Total - 0


Row 9 Total of all =0


Thanks

 

[awoohaw]

I would not recommend doing this work this way.
Having Data mixed up with calculations like this is a recipe for confusion and eventual errors.
But, this is an answer for your question:

Book1
	A	B
1
2	Ans1	5
3	Ans2	2
4	Ans3	10
5	Q1 Total	17
6	Ans1	5
7	Ans2	Auto Fail
8	Ans3	10
9	Q2 Total	0
10	Final Total	0
Sheet6

Cell Formulas
Range	Formula
B5,B9	B5	=IF(ISNUMBER(MATCH("Auto Fail",B2:B4,0)),0,SUM(B2:B4))
B7	B7	="Auto Fail"
B10	B10	=MIN(B5,B9,B5+B9)

 

[Cubist]

if both are numbers gives that values

In which circumstances row 4 and 8 are not numbers?

 

[Excelnovice13]

In which circumstances row 4 and 8 are not numbers?

They would be numbers always it’s just that sometimes it will be a zero if one of the answers to the questions is auto fail.

That’s why I was looking for a formula where if a row equals zero that the total sum defaults to zero

 

[Cubist]

Try this.

=AND(A4<>0,A8<>0)*SUM(A4,A8)