jake1997harrison
New Member
- Joined
- Jan 20, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi All,
I am attempting to use the standard sumproduct/subtotal/offset approach to dynamically sum a range based on given criteria. The criteria are the model an account is invested in and the target value of equity. All data is contained in a table and I’d prefer to use table references if possible so as to make the macro that writes this formula cleaner.
Here is my current formula using standard range references that returns the dreaded #N/A error. Help cleaning up my syntax or ideas for diagnosing the error would be much appreciated.
=SUMPRODUCT(SUBTOTAL(109,OFFSET(J41,ROW(J41:J349)-ROW(J41),)),—(AF41:AF349=“PIM 1”),—(P41:P349=80))
*due to the sensitive nature of the data in the spreadsheet and a corporate firewall, I cannot post the actual spreadsheet or screenshots of the spreadsheet.
I am attempting to use the standard sumproduct/subtotal/offset approach to dynamically sum a range based on given criteria. The criteria are the model an account is invested in and the target value of equity. All data is contained in a table and I’d prefer to use table references if possible so as to make the macro that writes this formula cleaner.
Here is my current formula using standard range references that returns the dreaded #N/A error. Help cleaning up my syntax or ideas for diagnosing the error would be much appreciated.
=SUMPRODUCT(SUBTOTAL(109,OFFSET(J41,ROW(J41:J349)-ROW(J41),)),—(AF41:AF349=“PIM 1”),—(P41:P349=80))
*due to the sensitive nature of the data in the spreadsheet and a corporate firewall, I cannot post the actual spreadsheet or screenshots of the spreadsheet.
