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.

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

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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