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

formulasgoogle sheets

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.

Best Answer

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)
Related Topic