Hi Steve. Because you are grouping things into uneven buckets, then the easiest way to do this is to add a formula into your source data that categorises the data into the appropriate group, and then bring this new field into your pivot.
Rather than use a nested IF statement to do this - which would be pretty complicated given the 9 different groups - it's far easier to use a Lookup table to group things. To do this, we need to sort the Lookup table in descending order, and use the MATCH function with a Match_type argument of -1, which looks up the smallest value greater than or equal to the lookup value.
I've done this in the attached file. I've also turned your PivotTable Source data into an Excel Table - which is something i ALWAYS do - so that when you add new records, then they will automatically show up in the PivotTable next time you refresh it (i.e. you don't have to use the 'Change Data Source' button to include them). So the below screenshots and formulas use Excel Table Notation. If you're unfamilar, then give Google a spin. I'll also be covering stuff like this in the book I'm working on -
Excel for Superheroes and Evil Geniuses.
A slight complication is the fact that you also want to see items that happen to be at the expiry date. To accommodate this, we need a grouping table that looks like this:
View attachment 12861
(Note that I might have the Text entries the wrong way around...I haven't studied your requirements that closely. This is easily fixed...just change the wording in the 'Text' column.)
I've given that table the name 'Groups'. The formula we use to categorize your source data based on that table looks like this:
=INDEX(Groups[Text],MATCH([@[Quote Dely Days to Exp]],Groups[Boundary],-1))
Here's a screenshot that shows the result:
View attachment 12862
See the attached file.
