Extracting data from pivot table based on 2 conditions – date range and a criteria

anup_d

New Member
Joined
Jun 3, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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)
1593504395297.png


Image 2)
1593504457589.png


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)
1593504497294.png


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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Anup,

To ease your ability to group periods, I would recommend that you go to your raw data sheet where all columns are located, add a column and use the following formula (Assuming the period 201507 is in cell A1)
=TEXT(Date(Left(A1,4),Right(A1,2),01),"mmm-yy")

Fill down the formula then, when you create the Pivot Table, you right-click the field and click on "Group"

This should help you with the other query as well

Regards
M. Yusuf
 

Watch MrExcel Video

Forum statistics

Threads
1,128,092
Messages
5,628,620
Members
416,329
Latest member
phxdan79

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
Top