Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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