1

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

migrated from superuser.com Aug 10 at 13:38

This question came from our site for computer enthusiasts and power users.

0

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.

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.