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?


3 Answers 3


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:


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.

  • Thanks. What does the dollar sign $ do in front of the coloumns?
    – Steeven
    Oct 3, 2013 at 10:26
  • It doesn't work for me - but I found that any IF command I make is not working. See this question: webapps.stackexchange.com/questions/50234/…. I will return, when that is solved.
    – Steeven
    Oct 3, 2013 at 10:52
  • The $ sign is used to make an absolute reference - in this case I was testing using different data in two columns and copying the formula from one cell to another, but I wanted to use a single column for the "subtotal" column. You don't need it for the solution - Thanks for noticing. I'll remove it for you. Oct 3, 2013 at 11:57

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: enter image description here


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



WA50197 example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.