GETPIVOTDATA - Can you reference subtotals?

Hi there, I hope I can explain this well... I've attached a portion of a Pivot table I have below. For the purpose of confidentiality, I've blurred out quite a bit but I believe there is enough there you'll be able to understand what I'm trying to do.

The first tab in my workbook is the pivot. I have a second tab that is essentially a copy of my raw data with "AccountName" in column A. In the next column I want to pull the "Total Sum of Premium Estimated" for that "AccountName" where at least one LineStatusCode is "NEW" within that "AccountName" - I'm perfectly fine getting a result of "#REF" if that particular AccountName doesn't have a "LineStatusCode" of "NEW" but if it DOES have that line status, then I need it to show the TOTAL "Sum of PremiumEstimated" (NOT the Sum for that particular line status). Is this possible?? (I have a very basic understanding of Excel, so please be gentle)

Referring to my sample data, if my second tab has A1 showing the Account Name of "Ra", I want A2 to pull in "$09.06" in such a way that I can just copy the formula down. So for "As" it would pull in #REF because there is no LineStatusCode of "NEW" for that Account.

Attachment 480004

Hi,

Using the layout from your picture, where the first instance of Ra is in A5, your formula might be

=IF(ISERROR(GETPIVOTDATA("Sum of Premiumestimated",$A$4,"Accountname",A5,"lookupcode",B5,"linestatuscode","NEW")),"",GETPIVOTDATA("Sum of Premiumestimated",$A$4,"Accountname",A5,"lookupcode",B5))

Assuming the same pivot layout as suggested by your first picture

=IF(ISERROR(GETPIVOTDATA("Sum of Premiumestimated",'More than 50k'!$A$4,"Accountname",A2,"linestatuscode","NEW")),"",GETPIVOTDATA("Sum of Premiumestimated",'More than 50k'!$A$4,"Accountname",A2))

However I do feel a sample workbook would remove much of the guesswork.

Try my last suggestion. It appears it should work from your latest formula.