sumproduct across multiple worhseets - how to ignore blank cells in worksheet list

[hishameen]

Hello,

Ive been trying to figure this out & im not really getting anywhere so I thought I'd just ask. Any help would really be appreciated.

Im using this formula below (copied exactly)

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$N$3:$N$20000"),INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$G$3:$G$20000"),$A$3,INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$I$3:$I$20000"),E8))

Basically, Id like to ignore any blank cells in the range of worksheet names the formula references (Settings!C2:C200). I would like to leave blank cells in the range so that in future should I need to add more sheets I would not have to go though and edit all the formulas. (I will be using a variation of this formula referencing the same range of sheets in well over 100 cells)

I do appreciate any help in this regard.

Thank You in advance.

 

[Robert Mika]

Hello,

Ive been trying to figure this out & im not really getting anywhere so I thought I'd just ask. Any help would really be appreciated.

Im using this formula below (copied exactly)

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$N$3:$N$20000"),INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$G$3:$G$20000"),$A$3,INDIRECT("'"&SETTINGS!$C$2:$C$200&"'!$I$3:$I$20000"),E8))

Basically, Id like to ignore any blank cells in the range of worksheet names the formula references (Settings!C2:C200). I would like to leave blank cells in the range so that in future should I need to add more sheets I would not have to go though and edit all the formulas. (I will be using a variation of this formula referencing the same range of sheets in well over 100 cells)

I do appreciate any help in this regard.

Thank You in advance.

Created a Named Range (call it list) which will refere to range with your data
=OFFSET(SETTINGS!$C$2,,,COUNTA(SETTINGS!$C$2:$C$200),)
The formula will look like this:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&list&"'!$N$3:$N$20000"),INDIRECT("'"&list&"'!$G$3:$G$20000"),$A$3,INDIRECT("'"&list&"'!$I$3:$I$20000"),E8))

 

[hishameen]

Created a Named Range (call it list) which will refere to range with your data
=OFFSET(SETTINGS!$C$2,,,COUNTA(SETTINGS!$C$2:$C$200),)
The formula will look like this:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&list&"'!$N$3:$N$20000"),INDIRECT("'"&list&"'!$G$3:$G$20000"),$A$3,INDIRECT("'"&list&"'!$I$3:$I$20000"),E8))

Thank you for the quick reply, thats exactly what I was looking for.

 

[Robert Mika]

Thank you for the quick reply, thats exactly what I was looking for.

You are welcome.Thanks for feedback.