IF statement in Google spreadsheet

Question

I am trying to sum up the total of all values in a coloumn that are POSITIVE. Also, I have some subtotals in the coloumn that should be left out from this sum.

=SUMIF(E6:E65 ; ">0" ) - SUM(FILTER(E6:E65;E6:E65>0;not(ISERROR(SEARCH("subtotal";C6:C65)))))

I can easily do a SUMIF to find only positive values. But Google Sheets doesn't have a SUMIFS command to let me sum those that are positive AND not with the subtotal string in the neighbour cell.

That's why I used the above. I sum all the positives and then subtract all positives that also have the subtotal string in the neighbour cell. The FILTER only let's those values through that are fullfilling both requirements.

This works fine mostly.

But I get an error (I get #N/A) when there are no subtotals with a positive number. In that case the SUM(FILTER... part of course doesn't give me a value, since it never summed anything. In that case, how can I make some kind of an if-statement, so I only make this subtraction, if what I subtract is in fact a number?

Answer 1

What you need is:

=ARRAYFORMULA( SUM( IF( ISERROR( SEARCH("subtotal", C6:C65)), IF(E6:E65 >=0, E6:E65, 0 ), 0)))

---

To break down the logic:

ARRAYFORMULA (SUM(

is the equivalent of doing:

= IF( ISERROR( SEARCH("subtotal", C6)), IF(E6 >=0, E6, 0 ), 0)

for each line, then summing the total. Note that you use a range reference (eg C6:C65) instead of a single cell reference (eg C6) when using ARRAYFORMULA.

Next it checks if subtotal is not in the (semi-) adjacent cell (otherwise it returns 0):

IF( ISERROR( SEARCH("subtotal", C6))

If "subtotal" is not in the adjacent cell, next check if the value is positive

IF(E6 >=0

If value is positive return the value, otherwise return 0.

Answer 2

Sorry, but your question is not entirely clear.

You could add another column right next to it and that column could contain all the positive values as your column "E"—something like =if( E6 > 0, E6, 0)—and do this for all the rows "6–65". And then you can do the sum of this other column.

Update: screenshot might hopefully explain better:

Answer 3

Google has moved on since this question was asked. New Google Sheets does have SUMIFS. For example:

=sumifs(E:E,C:C,"<>subtotal",E:E,">0") 

.