sum if help

[lparlato]

hi everyone!

i need to sum 4 cells in columns A-D...some of the cells have an error [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! which I want the formula to ignore.

Take Row 5 in table 1 below for examples: Cell A5 and B5 have numbers, but cell C5 and D5 have an erroe...i want cell E5 to only total the cells with numbers.

If ALL 4 cells contain [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! (SEE ROW 6) I would like the column E to display a blank cell.

TABLE 1

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Q1 GROWTH IN LEVELS[/TD]
[TD="align: center"]Q2 GROWTH IN LEVELS[/TD]
[TD="align: center"]Q3 GROWTH IN LEVELS[/TD]
[TD="align: center"]Q4 GROWTH IN LEVELS[/TD]
[TD="align: center"]ANNUAL GROWTH IN LEVELS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ![/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Foz1980]

In E2 use the below formula then drag down the rest of column E.

=SUM(IF(ISERROR(A2:D2),"",(A2:D2)))

This needs to be used as an Array so press Ctrl, Shift and Enter when you've input the formula.

 

[Weazel]

something like....

=IF(COUNTIF(A2:D2,"<1")>=1,SUM(A2:D2),"")