# 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

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.

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

Regards
M. Yusuf

Replies
2
Views
81
Replies
1
Views
82
Replies
0
Views
167
Replies
0
Views
229
Replies
1
Views
106

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.

### Which adblocker are you using?

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

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