Pivot table - Intervals

cpsnuggle

New Member
Joined
Jul 29, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to make a pivot table based on below. However, the intervals doesn't sum up correct.

I have two columns; Days and Name. Then I made some intervals ranging from 1 - 30 days and 101 -200 days, filtering out how many times the days hit the interval (=IF(AND(A18>=31;A18<=100);"31 - 100 days";""))

I would like to display how many times the intervals occur per name.

Thanks in advance!

1628060437270.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi. In order to get the best results in a pivot table, you'll want your interval data to only be in one column. Make a new interval column:
Excel Formula:
=IFS(A2<=30,"1 - 30 days",A2<=100,"31 - 100 days",A2<=200,"101 - 200 days")

This way if you select your three columns to add to the pivot table, all you need to do is set "inverval" as the columns, "name" as the rows, and "count of inverval" as the values. You'll get a pivot table as the picture below shows.

1628103011668.png
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top