Any help I can get on the below will be much appreciated, as its giving me sleepless nights as cannot figure out the correct formula to use.
Using a pivot tale and trying to extract the data, I am trying to calculate the total values from a date range, where the dates are horizontal, going over a number of columns (see image 1).
However, the dates in the pivot table (cell B4 and onwards) are a “period” and not regular months, the corresponding actual months are noted in cell B4 and onwards which are manual entered above the pivot table (does not form part of the pivot table).
I then need to add/sum the total values as noted above based on a second criteria, this being from cell A5 and downwards.
Thus, what I am looking to do overall for e.g. I need to sum for decant/migration (cell A5) moves from period 201607 (cell B4) to 201612 (cell E4). I need it as a formula as currently the data has been ‘collapsed’ and when expanded the data goes onto different cells (see image 2)
Image 1)
Image 2)
The format I am trying to take all this data to, is set out below for reference (image 3). I am trying to put the formula in column C21 to C33, using the criteria from column B21 to B33 an then hard coding the data into the formula.
Image 3)
Another Formula Request
There is also a request for a second formula? Using image 3, I need to use the criteria from cell B21 to B33 and then by months for each column, get/link the data from the pivot table in image 1 and 2.
For e.g. in image 3, cell D22, I need the formula to look at B22 (professional fees / surveys) and the date from either cell D18 (same format as the pivot table) or D19 (manual entered above pivot table) and get the corresponding values from the pivot table (images 1 & 2), I appreciate that in images 1 & 2 above the dates are do no extend as far in image 3 but they do in the actual file.
Appreciate any help or advice you give on.
Using a pivot tale and trying to extract the data, I am trying to calculate the total values from a date range, where the dates are horizontal, going over a number of columns (see image 1).
However, the dates in the pivot table (cell B4 and onwards) are a “period” and not regular months, the corresponding actual months are noted in cell B4 and onwards which are manual entered above the pivot table (does not form part of the pivot table).
I then need to add/sum the total values as noted above based on a second criteria, this being from cell A5 and downwards.
Thus, what I am looking to do overall for e.g. I need to sum for decant/migration (cell A5) moves from period 201607 (cell B4) to 201612 (cell E4). I need it as a formula as currently the data has been ‘collapsed’ and when expanded the data goes onto different cells (see image 2)
Image 1)
Image 2)
The format I am trying to take all this data to, is set out below for reference (image 3). I am trying to put the formula in column C21 to C33, using the criteria from column B21 to B33 an then hard coding the data into the formula.
Image 3)
Another Formula Request
There is also a request for a second formula? Using image 3, I need to use the criteria from cell B21 to B33 and then by months for each column, get/link the data from the pivot table in image 1 and 2.
For e.g. in image 3, cell D22, I need the formula to look at B22 (professional fees / surveys) and the date from either cell D18 (same format as the pivot table) or D19 (manual entered above pivot table) and get the corresponding values from the pivot table (images 1 & 2), I appreciate that in images 1 & 2 above the dates are do no extend as far in image 3 but they do in the actual file.
Appreciate any help or advice you give on.
