SumIfs Using Indirect based on 2 Criteria

[MINDFULLYLIZ]

Hi! I have been struggling for 5 days trying to get this formula to work. Basically, I need it to search across a few worksheets which I have named "JON" and sum a corresponding cell if it meets two conditions.

The formula returns a "Value" error:

=SUMIFS(INDIRECT("'"&JON&"'!$F$16:$F$65"),INDIRECT("'"&JON&"'!$A$16:$A$65"),$A5,INDIRECT("'"&JON&"'!A1:A1"),$B5))

Please tell me what I'm doing wrong.

Also note on the second criteria I've changed the "A1:A1" to just A1 and A1:A2 and A1:B2 to see if that helps

 

[MINDFULLYLIZ]

Try it like

=SUMPRODUCT((COUNTIF(INDIRECT("'"&JON&"'!A1"),B5))*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))

OH MY GOSH!!!!! THIS WORKS! Thank you so much!!!!!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[MINDFULLYLIZ]

Try it like

=SUMPRODUCT((COUNTIF(INDIRECT("'"&JON&"'!A1"),B5))*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))

Hi!!! Thought I responded earlier! But this worked!!!!! Thank you so much!!!!!!

 

[Fluff]

You did but it's the first post on page2, so you may have missed it.
But thanks again for the feedback.