How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

Question

I am wanting to create a formula that will look at a dozen different cells and sum to the total value of those cells but some cells will not be numbers and I want to exclude those cells in the sum.

For example:

D2=10
H2=IF(B1="BYO","Incl.",5)
M2=IF(A1="standard","Incl.",15)

then I want to have Z2=SUM(D2+H2+M2) but sometimes the values will be numbers other times they will be text.

I was playing around using IFS and trying to use syntax like <> "Incl." but I couldn't figure out how to make it work with different cells that may or may not be numeric.

Answer 1

Several Google Sheets built-in functions like SUM require that their arguments be numbers and if some of them are text in most of the cases they will return an error message. Exceptions are text values that could be coerced to numbers like numbers quote enclosed.

There are several built-in functions that could be used to handle unknown cell value types

• IFERROR If the first argument returns an error, returns the second argument
• ISNUMBER Returns true if the argument is a number value, false otherwise
• ISTEXT Returns true if the argument is a text value, false otherwise

the question doesn't include the expected result when the cells have a text value.

Let say that the expected result is 0, then we could use the following formula:

=IFERROR(SUM(D2,H2,M2),0)

Answer 2

Say we want to sum D1, E3, and F5, but only if they are numbers. If we use:

=sum(D1,E3,F5)

any of the cells that happens to be or become Text will be treated as zero:

The SUM() function already tests the cells to be sure they are numeric.

Answer 3

You are getting #VALUE! because you use =SUM(D2+H2+M2)

If you would use range then SUM works like you want to e.g. =SUM(D2:M2)

If you can't use range (like in your case), then you could move your sums under one column (and then hide it) like:

• N1: =D2

• N2: =H2

• N3: =M2

and then use =SUM(N1:N)
or simply use =SUM(D2+IFERROR(H2*1, 0)+IFERROR(M2*1, 0))
or mentioned =IFERROR(SUM(D2, H2, M2), 0)