# Calculating difference between totals in different pivot tables, where some fields do not match

#### Jules79

##### New Member
I have a single table of data that contains a number of different numerical/text fields against a record. They also have a calculated yes/no indicator depending on whether that record meets a particular criteria. I then have a number of pivot tables on another worksheet that calculates the number of records that meet this yes/no criteria. (COUNT) So I'll have one pivot table that pulls back 100% of my data. Pivot table 2 is adjacent to this and is filtered by where criteria 1 meets "yes". The 3rd pivot table is filtered by criteria 2 that meets "yes" and so on. Against these subsequent pivot tables I do a =F41/\$C41 formula to calculate the percentage of those "yes" counts against the first pivot table that contains a count of all records. This works perfectly well where the subsequent tables contain the same fields (ie the field names are adjacent to each other) but not where they don't.

If my 1st pivot table contains 6 records, but in my 2nd pivot which is filtered by yes it only pulls back 4 records, I still want to calculate the %age, but it has to match up agasint the matching fields. I'm currently doing this by manually moving the cells within the pivot table that the formula is look at each time I refresh the data, but there has to be a smarter way of doing this. I don't know if the =F41/\$C41 formula can incorporate some kind of vlookup or countif or whether I should use GETPIVOTDATA? Ideally I would like a calculated field within the pivot table put I don't know if that is possible when you have Yes/No values within the same column of data that you filter by on the pivot table. Hope that makes some kind of sense! Thank you.

EXAMPLE DATA
DEST REF SCAN DELIVERED
Birm 103 YES YES
Swin 104 YES NO
Duns 123 NO YES

PIVOT EXAMPLE 1
Scan = "Yes"
DEST
Birm 1
Swin 1

PIVOT EXAMPLE 2
Delivered = "Yes"
DEST
Birm 1
Duns 1

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Jules79

##### New Member
Apologies, my example pivot tables should say Example 2 and example 3. (The 1st pivot table would show a list of all 3 destinations and looks up all yes and no's.

Replies
1
Views
239
Replies
4
Views
351
Replies
0
Views
204
Replies
0
Views
131
Replies
0
Views
110

1,127,519
Messages
5,625,280
Members
416,086
Latest member
CaptainGD

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