Distribution function of *sums*?

[garyfritz]

I know I can use FREQUENCY() to count the items in a list and create a distribution function.

Is there any way to do that, but instead of *counting* the items, I *sum* a corresponding column?

Example: Imagine I have a spreadsheet that lists sales made in a store. It has two columns, price and #sold. So it might look like this:

$1.00, 2
$0.90, 1
$1.10, 3
$1.00, 5
$1.10, 3

So I want to create a distribution function on the bins $0.90, $1.00, and $1.10. FREQUENCY would return:

$0.90, 1
$1.00, 2
$1.10, 2

...which is a count of the # of sales at each price. But what I want is a count of the ITEMS SOLD at each price:

$0.90, 1
$1.00, 7
$1.10, 6

Is there a way to do that, short of extracting it manually?

Thanks!

 

[MikeWx]

Have you considered a pivot table with the dollar amounts Row Labels and the sum of the number sold as the Values?

 

[Ralajer]

You can use the sumif function.

Excel Workbook
	A	B	C	D	E
1	Price	# of Sales		Price	Total Sales
2	1	2		0.9	1
3	0.9	1		1	7
4	1.1	3		1.1	6
5	1	5
6	1.1	3
...

Cell Formulas
Range	Formula
E2	=SUMIF($A$2:$A$6,D2,$B$2:$B$6)

 

[garyfritz]

Mike, I have yet to fully "grok" pivot tables so I really don't know how to employ them in this situation.

Rob, SUMIF works when you're looking at exactly matches of 0.90, 1.00, etc. I want it to fit into ranges like FREQUENCY does. BUT I see that SUMIFS will do the trick for me, if I specify conditions of ">=0.9" and "<1.0". Maybe you could combine those conditions in SUMIF but SUMIFS definitely did the trick for me. Thanks!