Excel - Pivot table or Formula?

[Livia8]

Hi there,

I need to count the number of items that are below certain amounts, divided by the type of item.

For instance, I need to count how many coffees I've sold <=€5, how many between €5.01 and €10, how many between €10.01 and €15, and so forth. In column A I've the type of item (e.g. tea, coffee, pastry, etc), in column B I've the total cost.

What I need to obtain is a table that looks a bit like this:

I've tried with a pivot table but I can't get it to count on the basis of an amount/ amount range.

I haven't done this in many years and on a previous version of Office and Excel, so please forgive me as I'm very rusty, I know this is a very basic thing and the solution is probably staring at me in the face.

Many thanks.

 

[combo]

If your table has two columns, with single item in each row (type, cost of item), then in pivot table:
1) drag 'Type' to rows area,
2) drag 'Cost' to values area,
3) again, 'Cost' to columns area,
4) in pivot table, right-click any of the values in column headers, set required start, end and by values,
5) choose required aggregation type of values field (sum, count, average etc.),
6) if you need another aggregation, add 'Cost' to values area and choose type,
7) if you need orientation of data fields other than displayed, drag the 'sigma' symbol between rows and columns areas.

combo

 

[Livia8]

Thanks for that, Combo.

See, this is what I'm having a problem with:

4) in pivot table, right-click any of the values in column headers, set required start, end and by values

So far I've been able to count all the coffees, teas and pastries, and to calculate the total value of each, but I haven't been able to break those down by amount range.

I'd rather not have to resort to autofilter and sumproduct and copy and paste, as there are quite a lot of categories I've to go through.

Thanks again.

 

[strongm]

Which version of Excel. I ask because you should be able to do this relatively simply with Power Pivot (add-in included with Excel 20167, just needs to be activated) or downloadable from Microsoft for a few earlier versions. Yiu can then use it to add measures to a pivot table. Measures allow us to add new columns to the pivot table query using DAX (Data Analysis Expressions). For example, using the type of table discussed by combo, we might have:

 

[combo]

Sorry, I missed a part of sentence:
4) in pivot table, right-click any of the values in column headers, [highlight #FCE94F]click 'Group by...',[/highlight] set required start, end and by values,

combo

 

[Livia8]

Thanks folks, I'll try both methods and see what can I manage to do.