Good morning. I have a very large, changing daily, pivot table. I am using two pivot tables to get the totals I need, and I'm OK with that. In this exercise I needed to first figure out which month a particular new product started selling in each state. That is on pivot table one and it will show me the first month there were any sales for that product. On that same pivot table I was able to figure out how to take that first month and add on three more months (I need a full 3+ months' totals). The formula will change with each state if the first order month changes. I believe I'm OK with all these formula's, but not the next one.
Pivot Table #2 is the same layout as the first pivot table but the states are broken down further to the actual customer. Now I need to get an "Account Sold" total for the first month plus the next 3 full months. Even if that customer purchased something in more than one of the months I only want it counted once.
=IFERROR(SUM(INDEX(26:26,MATCH(D2,MONTH,0))+INDEX(26:26,MATCH(D2,MONTH,0)+1))+INDEX(26:26,MATCH(D2,MONTH,0)+2)+INDEX(26:26,MATCH(D2,MONTH,0)+3),"")
Above is the formula I used to get the sales totals from Pivot Table #1 with line 26 being the state AZ and MONTH being the range of the months across the top.
On the totals I need on pivot table #2 I'll need all the AZ Account Sold counts to add up so I think there should be a vlookup along with a count, but I'm totally lost on this one.
I would appreciate any help. Please note that the months across the top of the pivot table are always changing as well as the customers and states.
Bookmarks