GETPIVOTDATA formula returns #REF

[Yodelayheewho]

I have a spending plan and budget items are prioritized as A, B and C. I set up three scenarios so I could see what my budget at the end of the quarter would look like if 1) I spent money on 'A', 'B' and 'C' priority items, 2) only spent money on 'A' priorities, 3) If I spent money on 'A' and 'B' priorities.
I've discovered an anomaly. In the column that sums Priorities A & B, if the current quarter only has A''s, the GETPIVOTDATA formula returns a #REF error. If I change at least one of the A's to a B, the formula sums correctly. If I change ALL of the A's to B's, the formula sums correctly. Here is the formula:
=GETPIVOTDATA("sum of Balance",Pivot!$A$3,"QTR Ends",DATE(YEAR($K$24),((INT((MONTH($K$24)-1)/3)+1)*3)+1,1)-1,"Priority","A")+GETPIVOTDATA("sum of Balance",Pivot!$A$3,"QTR Ends",DATE(YEAR($K$24),((INT((MONTH($K$24)-1)/3)+1)*3)+1,1)-1,"Priority","B")
I even switched out the 'A' and 'B' in the formula, so 'B' was in the first half of the formula and no luck. I've attached a sample file.
Thank you for your consideration!

 

[Chihiro]

Well, it's because data does not exist in your Pivot for "Priority", "B"...

Take a look at your pivot and check "3/31/2021" grouping.
Only "A" and "C" are present. So looking for "Priority", "B" in "QTR Ends" = 3/31/2021 gives #Ref error. Since it is not present.
You can nest it inside IFERROR(formula,0) or other construct.

 

[Yodelayheewho]

Yes, your solution solves it. Simple. Thanks!

 

[Yodelayheewho]

Oops, I may have spoke too soon. So, I added IFERROR as recommended. If there is an error, the result is $0.00.
So, on the Spending Plan worksheet in cells H9:H17 here's the results of testing
Changed all entries to 'A' - result in M28 = $0, the result should be $726.09
Changed all entries to 'B' - result in M28 = $0, the result should be $726.09
Changed one of the entries to 'A' where the amt is $20, left the rest 'B' - result in M28 is $726.90, the result should be $726.90 - $20 = $706.90

The updated sample file with the IFERROR added is attached.

 

[Chihiro]

IFERROR should not be used on entire formula. Just the portion for "B". Or independently on each part.

Ex:

=IFERROR(GETPIVOTDATA("sum of Balance",Pivot!$A$3,"QTR Ends",DATE(YEAR($K$24),((INT((MONTH($K$24)-1)/3)+1)*3)+1,1)-1,"Priority","A"),0)+IFERROR(GETPIVOTDATA("sum of Balance",Pivot!$A$3,"QTR Ends",DATE(YEAR($K$24),((INT((MONTH($K$24)-1)/3)+1)*3)+1,1)-1,"Priority","B"),0)

 

[Yodelayheewho]

That did it! Thank you so very much!