Having trouble inserting OFFSET() as variable twice in formula

Hi everyone,

Long time lurker, first time poster. Online Excel 365.

I have several sheets that I am running through to total sections and I want them to be dynamic based on raw data placed in a master list. Here is what I have so far:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&OFFSET(UPN!$A$2,,,UPN!$F$4)&"'!"&"F2:F534"),INDIRECT("'"&OFFSET(UPN!$A$2,,,UPN!$F$4)&"'!"&"$A$2:$A$534"),$A2))

This works just fine, and the

&OFFSET(UPN!$A$2,,,UPN!$F$4)&

works great as a variable range.

However, instead of hard coding the

"F2:F534"

, I was hoping to apply the same principle that worked for the first variable range.

When trying:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&OFFSET(UPN!$A$2,,,UPN!$F$4)&"'!"&OFFSET('P1'!$F$2,,,'P1'!$K$1)),INDIRECT("'"&OFFSET(UPN!$A$2,,,UPN!$F$4)&"'!"&"$A$2:$A$534"),$A2))

so that the

OFFSET('P1'!$F$2,,,'P1'!$K$1)

just replaces the

"F2:F534"

, I get a reference error.

I must be doing something wrong with the quotes, but have tried in vain different combos and when I plainly type the

OFFSET('P1'!$F$2,,,'P1'!$K$1)

into a cell, it returns the correct array. Any idea what I am doing wrong? Thank you!