1

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.

0

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)
-1

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 cant use range, 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)

  • The second formula is not equivalent to the first one by the other hand if any of the cells in the range has a text value it will return an error too. – Rubén Nov 20 '18 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.