Selected Answer
Hi Sie (Isaac)
You can get that effect by using SUM and GETPIVOTDATA within an array formula (which in versions before 365, you would need to enter using Ctrl+Shift+Enter).
In the attached file, I modified your upper pivot table (only) by changing the Custom Field names (so e.g. it shows "April" rather than "SUM of Sum of April" as a column heading) and the £ sums to "Month value" so it makes a bit more sense on first glance.
Having done that, in the "Data" worksheet cell B6 I put this array formula:
=SUM(GETPIVOTDATA(""&$R3:R3,$Q$3,"Month Value",$Q$4:$Q$6))
This gives the sum of April to April (so just April!) but can then be copied right (or filled) so C6 is April to May, D6 is to June etc.
It works like this:
- The SUM is wrapped around the GETPIVOTDATA formula which extracts several "Month Value" figures (£)- the third argument in the GETPIVOTDATA part above.
- The second argument of GETPIVOTDATA specifies the table the data will come from (i.e that involving cell Q3, upper left in your pivot).
- The last argument $Q$4:$Q$6 fixes the rows for which the values in your pivot table are to be returned (i.e. Product 1, Product 2 and Product 3) and summed.
- The first argument ""&$R3:R3 sets the columns for which row data will be returned. $R3 fixes the leftmost header and is followed by R3 (with no $) which will increment as the formula is copied right (so becomes ""&$R3:S3 which equates to the columns headed April and May etc.). For reasons known best to Microsoft, you have to append a blank string (using ""&) to get this method of addressing to work within GETPIVOTDATA.
The nett result is that it recalculates the Grand Totals in your pivot table and you have a YTD (year to data) array formula!
REVISION:
The second attached file below also address the issue of YTD costs and income data per product.
In yellow-shaded cell A16 (of worksheet "Data"), list data validation provides a dropdown from which you can pick a month (from R3:AC3 of my revised pivot table), e.g. June.
Below that, in A17, the MATCH formula below determines which cell the chosen month is:
=ADDRESS(3,17+MATCH(A16,R3:AC3,0))
It uses MATCH with a third argument of 0 (for an Exact Match) but if you have Excel 365, you can use XMATCH instead (where that's the default). You could move that cell elsewhere (or set the font colour to match the background (so the user doesn't see it.
Then, for Cost data, a typical formula (in B18) is:
=SUM(GETPIVOTDATA(""&$R3:(INDIRECT(A17)),$Q$3,"Month Value","Product 1"))
where the second part of $R3:R3 (used above for YTD) is replaced by the INDIRECT formula which points to what A17 contains. Change A16 to read June, and it will point to T3 (which is in A17 for that choice).
The income formula is a bit different, e.g. in C18 we have:
=SUM(GETPIVOTDATA(""&$R10:OFFSET((INDIRECT(A17)),7,0),$Q$10,"Month","Product 1"))
since it looks in a different table but the wrap-around of OFFSET points to 7 rows below that in A17. That means as you change A16, the income formulae point to the right month from their pivot table.
Finally,D18:D21 calculate the percentage YTD profit margin (note it is margin, not mark-up).
Hope this works for you.
