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?
Best Answer
What you need is:
To break down the logic:
is the equivalent of doing:
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 "subtotal" is not in the adjacent cell, next check if the value is positive
If value is positive return the value, otherwise return 0.