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 `subtotal`s 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?

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.

• Thanks. What does the dollar sign \$ do in front of the coloumns? – Steeven Oct 3 '13 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 '13 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. – Tom Horwood Oct 3 '13 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: 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")
``````

. 