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?