Sumif searching several sheets
I have created the following formula, however, is there an easier way to include several sheets that may contain the lookup value?
=SUMIF(INDIRECT("'"&INDEX(SheetNames,$A13)&"'!$B$21:$B$31"),$B129,INDIRECT("'"&INDEX(SheetNames,$A13)&"'!D21:D31"))+SUMIF(INDIRECT("'"&INDEX(SheetNames,$A14)&"'!$B$21:$B$31"),$B129,INDIRECT("'"&INDEX(SheetNames,$A14)&"'!D21:D31"))
Perhaps
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!$B$21:$B$31"),$B129,INDIRECT("'"&SheetNames&"'!D21:D31")))
Not sure if the syntax is correct without testing, but there is definitely a way to do it.
Last edited by jason.b75; 02-20-2019 at 04:47 PM.
It would be easier to answer definitively if you were to upload the workbook and tell us which values you are trying to sum.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks. I may upload but I may also be getting close. Here I have modified the syntax as follows:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"$B$21:$B$31"),$B129,INDIRECT("'"&Sheets&"'!"&"D21:D31")))
However, the result I get is #NAME? Seems like it should be a simple fix but I can't find it.
Is it because not every sheet in the file has the same array? Most of them do but there are some that don't....
I figured it out. I needed to name a range as Sheets. Works great now!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks