Using a SUMIFS across multiple sheets with multiple criteria

Hello,

I have been making a spread sheet which collects allot of different data and then has a summary page at the end which adds specific bits of the data up. i have managed to get it working where it just pulls from one sheet but i am now needing to get this to work across multiple sheets.

This is what i have used for a single sheet:

=SUMIFS(habitatmins, habitat, "open", time, "SS-15 to SS+30",battype, "common pipistrelle")

habitatmins = $F:$F
habitat = $E:$E
time = $A:$A
battype = $G:$G

I have tried the following but all it returns is a zero:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$M$30:$M$31&"'!$F:$F"),INDIRECT("'"&$M$30:$M$31&"'!$E:$E"), "open",INDIRECT("'"&$M$30:$M$31&"'!$A:$A"),"SS+31 to SS+60", INDIRECT("'"&$M$30:$M$31&"'!$G:$G"), "common pipistrelle" ))

where my sheet names are in M30:M31 (but will be extended to a large number of sheets)

Any help would be much appreciated.

Many thanks,

Aaron

I would suggest that you put your SUMIFS formula in the same cell in each subsidiary sheet, let's say in X1. You can do this in one operation by grouping all those sheets together from first to last (excluding the summary sheet, which should be positioned outside this "sandwich" of sheets), and then putting in the formula:

=SUMIFS(F:F, E:E, "open", A:A, "SS-15 to SS+30",G:G, "common pipistrelle")

in X1. Note there is no need for named ranges. You can ungroup the sheets by right-clicking one of the sheet tabs and selecting Ungroup sheets.

Then in your summary sheet you can have this formula to add them all up:

=SUM('first:last'!X1)

Change the sheet names as appropriate, or leave them like that in the formula and create two blank sheets called first and last and position them to make a "sandwich" of your subsidiary sheets - then you can add as many as you need in the future and just drag the sheet tabs into that area.

Hope this helps.

Pete

The only problem with me putting in the SUMIFS in each sheet is that the values which my SUMIFS are comparing and adding up there is over 100 different conditions i have.

I have attached my workbook below so you's can get an idea of what i am trying to do.


Surevy Form.xlsx

i see i could do it by making a summary table at the bottom of each sheet and then pulling each one of them together. I just would of liked to try and make it look neater without them :P

Or you could position the summary table to the right of your data (beyond column M), and then you can still use full-column references without inadvertently getting circular reference errors. Just list your different conditions in 3 columns, and then change the SUMIFS function to refer to those columns.

Hope this helps.

Pete

that is a good point, thank you

i'll give it a bash the now