subtotal by a range

  • Thread starter Thread starter gls858
  • Start date Start date
G

gls858

I have a list of items sold. Each row contains, the selling price, the
cost, and the gross margin percent.
Is it possible to subtotal the selling price by a range of gross margin?

Example: I would like to see the total sales between 0 and 15 gross
margin, 15 to 25, 25 and above.

gls858
 
Column A = Item
Column B = selling price per
Column C = price for this sale
Column D = cost
Column E = gross margin

"Total sales" you asked for...is that a sum of the values in column C that
match the gross margin range?

0 to 15
=SUMPRODUCT(--($E$1:$E$100<=15),$C$1:$C$100)

15+ to 25
=SUMPRODUCT(--($E$1:$E$100>15),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$100>25),$C$1:$C$100)
 
JBeaucaire said:
Column A = Item
Column B = selling price per
Column C = price for this sale
Column D = cost
Column E = gross margin

"Total sales" you asked for...is that a sum of the values in column C that
match the gross margin range?

0 to 15
=SUMPRODUCT(--($E$1:$E$100<=15),$C$1:$C$100)

15+ to 25
=SUMPRODUCT(--($E$1:$E$100>15),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$100>25),$C$1:$C$100)
Click to expand...

Yes that is correct. Total sales is the sum of the values that match the
range.

I'll give these formulas a try and post back the results.

Thanks
 
JBeaucaire said:
Column A = Item
Column B = selling price per
Column C = price for this sale
Column D = cost
Column E = gross margin

"Total sales" you asked for...is that a sum of the values in column C that
match the gross margin range?

0 to 15
=SUMPRODUCT(--($E$1:$E$100<=15),$C$1:$C$100)

15+ to 25
=SUMPRODUCT(--($E$1:$E$100>15),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$100>25),$C$1:$C$100)
Click to expand...

I made the necessary adjustments for columns and such and it appears to
have worked just fine. Thanks a bunch.

When I sorted my list by gross margin and then cut and pasted the ranges
into separate sheets and then total the sales column my totals were
slightly different than the totals for the <15 and the 15 to 25. I have
a few line items that have a negative gross margin could that be the issue?

gls858
 
gls858 said:
I made the necessary adjustments for columns and such and it appears to
have worked just fine. Thanks a bunch.

When I sorted my list by gross margin and then cut and pasted the ranges
into separate sheets and then total the sales column my totals were
slightly different than the totals for the <15 and the 15 to 25. I have
a few line items that have a negative gross margin could that be the issue?

gls858
Click to expand...

Never mind I think I found the problem. Underlying values were
calculated to 5 digits. I was working with only 2 digits displayed.
differences were due to rounding.

gls858
 
Back
Top