subtotal by a range

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
 
J

JBeaucaire

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)
 
G

gls858

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)

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
 
G

gls858

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)

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
 
G

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top