SUMIFS and Indirect Formula

[F4TMAN]

Hello,

I'm not too familiar with indirect functions and I was trying to incorporate into my sumifs function. My current sumifs function is
=SUMIFS(FY20E!H:H,FY20E!A:A,$M4,FY20E!K:K,$J4,FY20E!E:E,$O4)

when i try adding the indirect function i ended up with this
=SUMIFS(indirect(A5&"!H:H"),A5&"!A:A", $M3,indirect(A5&"!K:K"),$J3,indirect(A5&"!E:E"),$O3))

I think im very close to getting it done but i keep getting the "there's a problem with this formula" error. Does anyone know what I'm doing wrong?

Thanks in advance

 

[RoryA]

Welcome to the forum.

It looks like you missed an INDIRECT out. Also, it's safer to wrap the sheet name in single quotes:

=SUMIFS(indirect("'"&A5&"'!H:H"),INDIRECT("'"&A5&"'!A:A"), $M3,indirect("'"&A5&"'!K:K"),$J3,indirect("'"&A5&"'!E:E"),$O3)

You should also be aware that INDIRECT is volatile, so it will recalculate whenever your workbook does, even if none of its inputs have changed, and that can slow down the workbook if you have a lot of these formulas.
​

 

[F4TMAN]

RoryA ...... I LOVE YOU!!!! lol thanks man you're a genuis you have no idea how much time you saved me.

 

[RoryA]

Glad to help.