GETPIVOTDATA Sum of multiple results

[BlitzTwig]

Hello everyone.
First post, I am for the most part an excel noob, but managed to 'wing it' so far, but having just spent several hours on this, and starting to pull what little hair I have left out... I hope someone can help me with a solution.
I have a pivot table producing data from a database, the database table is populated by a vba userform that records production run times from the shopfloor.
This is all good now, having spent ~8 months coding and tweaking it to our needs.

Now I'm trying to pull the correct data from the pivot table into a excel BOM that is created by Solidworks.
Im using the getpivotdata and get most data to pull through correctly, but only if the part has a single operation, and therefore only appears under the first 'op1' row.
The problem is I need to get all the data from op1, op2, etc and sum these together. I need it to variably check all of them, I don't want to manually assign every multiple op parts references.
Every job has an op1, most stop at that. Some may have op1, op1.1, op2, op3, etc. Its not fixed.

So as example, you can see CG1103A is in op1 and op2 but #ref on the bom.

The 999 in the cell above is =IFERROR(999, SUMPRODUCT(GETPIVOTDATA("M/C RUN TIME",'[ArmorTek Job Booking_BETA.xlsm]Completion List'!$A$5,C$1,$C2)))



any help very much appreciated.

 

[RoryA]

If you don't need OP NUMBER in the pivot, then just remove the field and your getpivotdata will work. If you do need it, I'd create a copy of the pivot table without that field and use that as your formula source.

 

[BlitzTwig]

T

If you don't need OP NUMBER in the pivot, then just remove the field and your getpivotdata will work. If you do need it, I'd create a copy of the pivot table without that field and use that as your formula source.

Thankyou for the reply.
Sadly, although I don't need the op number per se as the final data is the various ops added together, I do need the data segregated by op as they are averages of several days recorded data.
Removing that segregation averages the run time of, say 3 ops, together, which is total nonsense.
If this can't be done without mass complication I may just try pivot the pivot, and extract that..

 

[RoryA]

You might be better off with a SUMIFS formula then, assuming the pivot doesn't change layout (i.e. you will always want data from the same columns)

 

[BlitzTwig]

Thank you, Ill look into that function and see if I can get it to work for me.
Layout of the pivot will never change, just filters adjusted to pull the correct projects part data.

 

[BlitzTwig]

I had a play with the SUMIFS, I'm sure with more time I could have got it right, but, in the end I just made a pivot of the pivot, and that's working so