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]

 

[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]

Did you see post #20 ?

 

[RixRox]

That seems to have worked, but, I'm not going to get excited until you have had a chance to weigh in.

[/URL]Capture10 by RixRox, on Flickr[/IMG]

[/URL]Capture11 by RixRox, on Flickr[/IMG]

 

[Jonmo1]

I'm not going to get excited until you have had a chance to weigh in.

I thought I'd already been weighing in this whole time.. ? lol, j/k

Yes, looks right now.

 

[RixRox]

You have, and, patiently I might add. Thanks for not giving up on me Jon!

Rix

 

[Jonmo1]

You're welcome.

 

[FDibbins]

@ Jonmo, nicely done

@ RixRox Thanks for sticking with this, and also being patient