Google-sheets – Conditional row sum

formulasgoogle sheetsgoogle-sheets-arrayformula

I'd like to sum a column of cells if the corresponding cell on the next column contains a string of any sort. I've tried using SUMIF but I can't figure out how to combine it with ISBLANK or ISTEXT.

For example, in this case the function should output 12.

---------
| 5 | x |
---------
| 6 |   |
---------
| 7 | y |
---------

Best Answer

Here's the formula you need 

 =SUMIF(ARRAYFORMULA(ISTEXT(B1:B3)),TRUE,A1:A3)

First parameter :

Range (required) - the range of cells that should be evaluated by criterion.

The formula expects a range. This is why ISTEXT(B1:B3) alone doesn't work because it returns a single Boolean FALSE.

To return a range, you need to use ARRAYFORMULA()
I.E : ARRAYFORMULA(ISTEXT(B1:B3)) returns this 

--------
| TRUE | 
--------
| FALSE|
--------
| TRUE |
--------

Second parameter :

Criterion (required) - the condition to be met

You choose when a value will be SUM; in this case it's when ISTEXT() returns TRUE

Third parameter :

Sum_range (optional) - the range in which to sum numbers. If omitted, then range is summed.

In our case the range we are testing is different from the range we want to sum.

Related Topic