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

<tbody>

Tab name	Category
Sheet1	City1
Sheet2	City2
Sheet3	City1
Sheet4	City2
Sheet5	City1
Sheet6	City1
Sheet7	City1
Sheet8	City1
Sheet9	City1
Sheet10	City1
Sheet11	City1
Sheet12	City2
Sheet13	City1
Sheet14	City2
Sheet15	City2
Sheet16	City2
Sheet17	City1
Sheet18	City2
Sheet19	City1
Sheet20	City2
Sheet21	City1
Sheet22	City1
Sheet23	City2
Sheet24	City1
Sheet25	City2
Sheet26	City1
Sheet27	City1
Sheet28	City1
Sheet29	City2
Sheet30	City1
Sheet31	City1
Sheet32	City1
Sheet33	City1
Sheet34	City1
Sheet35	City2
Sheet36	City1
Sheet37	City2
Sheet38	City1
Sheet39	City2
Sheet40	City1
Sheet41	City2
Sheet42	City1
Sheet43	City1
Sheet44	City1
Sheet45	City2
Sheet46	City2
Sheet47	City1
Sheet48	City2
Sheet49	City1
Sheet50	City2
Sheet51	City1
Sheet52	City1
Sheet53	City2
Sheet54	City2
Sheet55	City3
Sheet56	City3
Sheet57	City3

<colgroup><col><col></colgroup><tbody>
</tbody>

</tbody>

 

[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!