Pivot Table Counts

kiki443

Board Regular
Joined
Feb 6, 2005
Messages
135
How can I use Pivot table to count based on criteria?

I have a pivot table report and it's broken down by type of invoice, region, and products. There is also a column for the age of the invoice.

I want to see the number of invoices that are in a specific region, with specific products, and are 0-5, 6-15, and 16-30 days old.

This is my 2nd attempt at a pivot table and I'm stuck at this point.

Any help is appreciated. Thanks
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
kiki443,

Can you add a column to your data to indicate which time bucket the invoice is in?

Dufus
 

kiki443

Board Regular
Joined
Feb 6, 2005
Messages
135
Pivot table counts

I can add another column but there will be six different buckets. Should I use nested countifs?

Not sure how to put them in the right bucket.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
The pivot table will put them in the time buckets, you just need the actual age, which should just be a subtraction
 

kiki443

Board Regular
Joined
Feb 6, 2005
Messages
135

ADVERTISEMENT

I have the age column but it counts how many there are per age. How do I get them into the right bucket.

If I do it before creating the pivot table won't I need to use countif or something to get the bucket it should be in?

How can I do it directly from the report?
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Select one of your ages, which will either be in the row or column field, right click and select group and outline then group.
 

kiki443

Board Regular
Joined
Feb 6, 2005
Messages
135
Thanks, that seems to work, I just have to tweak the criteria for the buckets so that it will reflect the ones I need.

Than you again.
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

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