sum cells in sheets that meet criteria

[tomanton]

hello folks

I'm trying to sum cell values accross different sheets in a wb, depending whether a sheet falls under a certain category
I have a summary sheet where all the sheets are simply added toghther. there are about 40-50 sheets and each of them fall in 1 out of 3 categories. So i listed sheet names and added next to them which category they belong to, so depending on sheet's category i could sum values of relevant sheets only, but i cant seem to find a solution to it.
i.e. i want to sum values of C6 cells in all sheets that are in category 3. Thanks
[TABLE="width: 500, align: center"]
<tbody>[TABLE="width: 217"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tab name[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet6[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet7[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet8[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet9[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet10[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet11[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet12[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet13[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet14[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet15[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet16[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet17[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet18[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet19[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet20[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet21[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet22[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet23[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet24[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet25[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet26[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet27[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet28[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet29[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet30[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet31[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet32[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet33[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet34[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet35[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet36[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet37[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet38[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet39[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet40[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet41[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet42[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet43[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet44[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet45[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet46[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet47[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet48[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet49[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet50[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet51[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet52[/TD]
[TD]City1[/TD]
[/TR]
[TR]
[TD]Sheet53[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet54[/TD]
[TD]City2[/TD]
[/TR]
[TR]
[TD]Sheet55[/TD]
[TD]City3[/TD]
[/TR]
[TR]
[TD]Sheet56[/TD]
[TD]City3[/TD]
[/TR]
[TR]
[TD]Sheet57[/TD]
[TD]City3[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]

 

[Special-K99]

Google 3d SUMIF multiple sheets

 

[tomanton]

researched with no avail. sumif works for range and i need to sum specific cells in the sheets that meet validations. any ideas?

 

[admiral100]

Hi,

Try this

SheetList = all your sheets names
C4 in each sheet contain the value 3 (category 3)
D4 the value to sum

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&SheetList&"'!D4"),,),INDIRECT("'"&TabList&"'!C4"),3))

 

[tomanton]

As i have solved the issue i want to share it so someone may find it any other uses:
Column P contains Sheet names
Column R contains condition for sheets (sheets belong to either HSL or LPH group)

i want to sum up values of cells B7 across sheets that belong to HSL group and the formula that worked for me was this
=SUM(IF(R1:R3="HSL",SUMIF(INDIRECT("'"&P1:P3&"'!$B$7"),"<1E100")))
confirmed with Ctrl+Shift+Enter as it's an array formula

good luck!

 

[tomanton]

Thanks for taking time to play with it and suggest a solution!