Add Sum if function in GetPivot

[Biplab1985]

Hi,

Need help to plug in Sum if function in to existing GetPivot function, so that the sum functions @ cell range and not on pivot table.

The reason i need this is because i noticed a variance in result when we use GetPivot and sum if requires defined cell range to work. I just need to keep the table dynamic by Get pivot but i what to do it at cell range.

The Formula gives the result for total volume based on multiple criteria.

=IFERROR(GETPIVOTDATA("Sum of Volume",$A$60,"DataType",C$10,"Year",TEXT(C$11,"####"),"Select Type",$B13),0)

Thanks!

[Biplab1985]

Any Suggestions?

[xlnitwit]

Hi,

I think a sample workbook would help immensely. I for one have no idea what it is you need from your post.

[Biplab1985]

Attached is a sample format. I have used a small data set, Hope its is adequate for to fix the issue.

[xlnitwit]

OK. Now can you explain with specific references, what it is you are trying to achieve?

[Biplab1985]

Hi, I have used Getpivotdata formula to pull the sum of volume which refers the pivot table, i need to use a formula as dynamic as Getpivotdata to refer the cell range and calculate sum of volume.

[xlnitwit]

That's basically what you already said. Can you explain using specific cell references what you want to add up and where you want the total to be?

[Biplab1985]

Yes, With cell reference i meant the sumif will start at the cell range level(Column C63:C70) in here for summing up for the three brands. The total will be in the same cells as it is now.

[JeteMc]

Try the following:
1) Turn the data on the 'Input Data' sheet into a table (the table name referenced by the formula is Table1)
2) Select cell C13 on the 'PINC Scorecard' sheet,
3) Paste the following formula into the formula bar:

=SUMIFS(Table1[[Volume]:[Volume]],Table1[[DataType]:[DataType]],C$10,Table1[[Year]:[Year]],C$11,Table1[[Brand]:[Brand]],$B13)

4) Once activated (by pressing Ctrl + Enter) drag the fill handle of cell C13 over to E13 and then (while C13:E13 are selected) down to E18.
When rows are added to the table (by selecting Z60 and pressing the tab key) the formula will automatically include the data.
Let us know if you have any questions.

[Biplab1985]

Hi,
Thanks for replying. But i cannot do that, i have entire macro created with worksheet change event. All the result should be from the pivot table2 below in the PINC scorecard WS. The reason i need the result from cell range because i noticed that sum of volume from Pivot result is not matching with when i use sumif function.

I just need to integrate SUMIF into GETPIVOTDATA if possible.