Sorting numbers into ranges i.e 0-1000,1001-2000 and getting count

[mikew7]

Please help,
I need to sort the following

type cost

apple 800
banana 1000
cherry 1500
banana 1050
banana 600
cherry 2500
apple 0

so that it sorts by type and then does count between ranges 0-1000
1001-2000, 2001-3000
i.e

type 0-1000 1001-2000 2001-3000
apple
banana "with a count here "
cherry

thanks
mik

 

[Frank Kabel]

Hi
one way:
1. Add a helper column to show your type. e.g. enter the following in
column C / cell C1:
=MAX(INT((B1-1)/1000),0)*1000 & " - " &
(MAX(INT((B1-1)/1000),0)+1)*1000
and copy this down for all rows

2. Use a pivot table to create your report. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Domenic]

Hi Mike,

Assuming that your data are in Columns A and B, with your headings i
Row 1, and your data starting in Row 2, try the following:

D2, and copy down:

=IF((A2<>"")*(ISNA(MATCH(A2,$A$1:A1,0))),A2,"")

Enter your type headings in Row 1, starting at E1, for example 0-1000
1001-2000, etc.

E2, copy across and down:

=SUMPRODUCT(($A$2:$A$8=$D2)*($B$2:$B$8>=--LEFT(E$1,FIND("-",E$1)-1))*($B$2:$B$8<=--RIGHT(E$1,LEN(E$1)-FIND("-",E$1))))

Hope this helps!

 

[mikew7]

thank you both for your help, I will try these out!
Mike