ExcelBanter - subtotal by a range

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - subtotal by a range (https://www.excelbanter.com/new-users-excel/226857-subtotal-range.html)

subtotal by a range

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

subtotal by a range

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$10015),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$10025),$C$1:$C$100)

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"gls858" wrote:

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

subtotal by a range

JBeaucaire wrote:
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$10015),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$10025),$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

subtotal by a range

gls858 wrote:
JBeaucaire wrote:
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$10015),--($E$1:$E$100<=25),$C$1:$C$100)

25+
=SUMPRODUCT(--($E$1:$E$10025),$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

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