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]

 

[Jonmo1]

As a matter of fact, I did test it.
I did it again now, and here are the results.

It does not ignore the subtotals.

Book1
	H	I	J
8	72		583
9	98		-187
10	-41
11	10
12	Subtotal
13	139
14	-12
15	-18
16	-17
17	50
18	42
19	Subtotal
20	45
21	-99
22	92
23	21
24	Subtotal
25	14
Sheet1

Cell Formulas
Range	Formula
J8	=SUMIFS($H$8:$H$104,$H$8:$H$104,">0",$H$8:$H$104,"<>Subtotal")
J9	=SUMIFS($H$8:$H$104,$H$8:$H$104,"<0",$H$8:$H$104,"<>Subtotal")
H13	=SUBTOTAL(9,H8:H11)
H20	=SUBTOTAL(9,H13:H18)
H25	=SUBTOTAL(9,H20:H23)

 

[Jonmo1]

Now if you DO have the word Subtotal within the same column as the numbers you want to sum.
Are those words one cell above the actual subtotal formula?

In my example above, H12 has the word subtotal, then H13 has the actual subtotal formula.
If that is the pattern you have as well, you can offset the criteria range testing for the word subtotal.
=SUMIFS($H$8:$H$104,$H$8:$H$104,">0",$H$7:$H$103,"<>Subtotal")

 

[RixRox]

I'm not the experts guys, I just came here looking for help. Here are two screenshots of the excel template. If you can figure out why it does or doesnt work, great. All I know is that it works perfectly. I think the confusion lies in the way I'm using it. I'm summing the totals in separate areas.

[/URL]One by Rick Baehre, on Flickr[/IMG]

[/URL]two by Rick Baehre, on Flickr[/IMG]

 

[RixRox]

I'm not the experts guys, I just came here looking for help. Here are two screenshots of the excel template. If you can figure out why it does or doesnt work, great. All I know is that it works perfectly. I think the confusion lies in the way I'm using it. I'm summing the totals in separate areas.

[/URL]One by Rick Baehre, on Flickr[/IMG]

[/URL]two by Rick Baehre, on Flickr[/IMG]

This example shows some positive values (same formulas):

[/URL]Untitled by Rick Baehre, on Flickr[/IMG]

 

[Jonmo1]

According to your pictures, you DO have another column that can be used to indicate which rows are subtotals. Like I asked for in the beginning.
Look at all those columns on the left that say "Subtotal of Direct Labor..."
That is Perfect.

Try this, assuming column G contains those strings.
=SUMIFS($H$8:$H$104,$H$8:$H$104,">0",$G$8:$G$104,"<>Subtotal*")

 

[RixRox]

Column G is for Shop hours and Column H is for Field hours. and wont, necessarily be the same. It works perfectly the way it is, just accept it, lol. Again, I thank you for your help!!!

 

[Jonmo1]

No, I won't. And you shouldn't either.
I'm trying to help you.

That formula is NOT doing what you think it's doing. It is NOT ignoring subtotals.
Look, your sumifs for >0 is returning 13,680
I can only come up with 13,680 if I add ALL the values, INCLUDING your subtotals.

According to the picture, the values >0 that are NOT subtotals in that column, the sum should be 9360.


The picture doesn't help a lot because I can't see column and row headers. So I don't know which values are within the ranges specified by your formulas.

That column on the left (regardless if it's used for something else) does seem to indicate which rows are subtotals.
You should use that to your advantage as suggested.

 

[Jonmo1]

If Column G is Shop Hours, then it's column D (Description) that should be used in the last formula I suggested.
Column D contains the strings like "Subtotal of Direct Labor..."

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

 

[RixRox]

Yep, I see it now ... I've been looking for the perfect formula for so long, I saw a mirage and I drank the sand.

I tried inserting the formula you suggested, where you suggested, to no avail. Perhaps I put it in the wrong cell; Also, I have many columns (with subtotals) to sum, not just the labor columns, so I have returned to my original formula(s).

Calculated from I:118 =SUMIF(I7:I63,">0")+SUMIF(I65:I73,">0")+SUMIF(I75:I83,">0")+SUMIF(I85:I88,">0")+SUMIF(I90:I103,">0")+SUMIF(I105:I114,">0") which calculates the correct number ever so manually...

Thanks for your patience.

Picture with reference:

and,

 

[Jonmo1]

OK, it looks like it's column B that contains those text strings beginning with the word subtotal.
Try
=SUMIFS(I7:I114,I7:I114,">0",B7:B114,"<>Subtotal*")