Pivot Table Question

YQG

Board Regular
Joined
Sep 16, 2005
Messages
62
I have a list of Sales people showing each sale made by $ amount

eg

smith 11,250
smith 1, 000
jones 2,220
jones 900

I can get the pivot table to count the # of sales; sum the $ sold, etc., by salesperson, but I would also like it to count the # of sales between $ amounts, eg # sales < $1000; # sales between $1000 and $2000 etc.

So in the end I could see

Name - # Sales - $ Sold - # sales <$1000 - etc


Can anyone help.
Thanks to all for reading this, whether you can help or not.

S.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Something like this
Book1
ABCDEFGH
1namesales
2smith11250Sum of salessales
3smith10name0-9992000-299911000-11999Grand Total
4jones2220jones90022203120
5jones900smith101125011260
6Grand Total91022201125014380
Sheet1



Put your sales amounts both in the data field and the column field.

Then right click on the column headers in pivot table and select the group option
 

YQG

Board Regular
Joined
Sep 16, 2005
Messages
62
Thank GorD, that's exactly what I want but when I try to Group, excel is telling me "Cannot group that selection" ?? I had tried that already but when I got the error message I thought I was doing something wrong... obviously I am, I'm just not sure what. Any thought on why it won't group for me??

Thanks
 

YQG

Board Regular
Joined
Sep 16, 2005
Messages
62
Okay... I tried using a different $ amount field and it worked... it just won't on the $ amount field I want ???? Ahhh.... I had blank cells..... I've replaced blanks with 0.00 now it works.

Thanks GorD !! :)
 

Forum statistics

Threads
1,141,937
Messages
5,709,423
Members
421,636
Latest member
kelseyacheson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top