Exclude Subtotal Amounts from a sumif formula.

[RixRox]

I have negative and positive numbers (hours) that I want to sum from several categories on my spread sheet. Each category has a subtotal.

I want to sum both positive and negative hours in one column (start to finish) without including the subtotals. Any help would be much appreciated. Thank-you.

[TABLE="width: 400"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1146"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 1[/TD]
[/TR]
[TR]
[TD] -1[/TD]
[/TR]
[TR]
[TD] 2[/TD]
[/TR]
[TR]
[TD] -2[/TD]
[/TR]
[TR]
[TD] 3[/TD]
[/TR]
[TR]
[TD] -3[/TD]
[/TR]
[TR]
[TD] 4[/TD]
[/TR]
[TR]
[TD] -4[/TD]
[/TR]
[TR]
[TD] 5[/TD]
[/TR]
[TR]
[TD] -5[/TD]
[/TR]
[TR]
[TD] 10[/TD]
[/TR]
[TR]
[TD][TABLE="width: 573"]
<tbody>[TR]
[TD]10.00 [/TD]
[TD]subtotal =SUBTOTAL(9,A2:A12)[/TD]
[/TR]
[TR]
[TD]-15.00[/TD]
[TD]negative hours =SUMIF(A2:A13,"<0")[/TD]
[/TR]
[TR]
[TD]35.00[/TD]
[TD]positive hours =SUMIF(A2:A13,">0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]You can see that the subtotal is being added to the positive hours.[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[TABLE="width: 573"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=SUMIF(A2:A13,"<0")[/TD]
[TD]negative hours[/TD]
[/TR]
[TR]
[TD]=SUMIF(A2:A13,">0")[/TD]
[TD]positive hours[/TD]
[/TR]
</tbody>[/TABLE]

 

[palaeontology]

why does your formula extend to A13 ?

not exactly sure what your cell entry addresses are, but shouldn't it stop at A12 or even A11 ?

Kind regards,

Chris

 

[RixRox]

As I stated above, there are several categories with subtotals, I only included one in the example. So, after five or so rows there would be a subtotal and then rows six through 20 would have another, etc. Thank-you.

 

[RixRox]

Correction rows 7 through 20...

 

[Jonmo1]

Is there anything in another column that can differentiate between a subtotal row and all other rows?
So you can do something like
=SUMIFS(A2:A13,A2:A3,">0",B2:B13,"<>Subtotal")

 

[RixRox]

I tried it, but, there was an error stating that I entered to few arguments. Thanks for your help though!
-Rix

 

[RixRox]

NO Wait! It worked. I had missed the fact that you referenced the same column twice. In answer to our question, no there is not another column I can reference to differentiate the subtotaled rows, so, I referenced the same column a third time and that did it!

Holy crap, thank-you!

Here are the actual formulas I'm using in the spread sheet:

=SUMIFS($H$8:$H$104,$H$8:$H$104,">0",$H$8:$H$104,"<>Subtotal")
=SUMIFS($H$8:$H$104,$H$8:$H$104,"<0",$H$8:$H$104,"<>Subtotal")

Thanks Again,
-Rix

 

[Jonmo1]

That makes no sense, and in no way excludes any subtotal values from your sum.
It's excluding any cell containing "Subtotal" in $H$8:$H$104.
That's the same range that you're summing.
So any cell containing "Subtotal" in $H$8:$H$104 will already be excluded...because it's a text string, so it can't be summed in the first place.

 

[RixRox]

Whether it makes sense or not doesn't matter to me. It works. And I'd appreciate it if you would try it yourself before you go casting aspersions upon my post. I'm telling you, it's working for me.

 

[FDibbins]

I happen to agree with Jonmo.

You are summing H, based on values on H being >0
SUMIFS will ignore any text entry anyway, so adding that last criteria - based in the same column as the values - is redundant
=SUMIFS($H$8:$H$104,$H$8:$H$104,">0",$H$8:$H$104,"<>Subtotal"))

Jonmo's suggestion was based on the values being in column A and text being in column B.
When someone of Jonmo's level of experience and knowledge (43 000 posts in this forum alone) says something doesnt make sense, newbies (like yourself) and others should make note of that, and try to understand what needs to change to make it make sense. If what you have, is working - given the data you are basing it on- that's fine, but based on your request, that formula you gave may not work on a broader range of data.

Perhaps a sumifs() might be a better option to use instead of the subtotal() anyway?