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

#### anup_d

##### New Member
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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

##### Board Regular
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,678
Messages
5,838,734
Members
430,566
Latest member
ChanchalSingh

### 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?

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