VALUE error when using a range of sheets in formula

[MStacey]

I am trying to use a formula to SUM the values from a the same range of cells on multiple sheets and could do with some help please

A formula that works is:

=IF(C8="","",SUMIFS('Month (1)'!D$8:D$400,'Month (1)'!B$8:B$400,C8))+SUMIFS('Month (1)'!G$8:G$400,'Month (1)'!E$8:E$400,C8)
+SUMIFS('Month (2)'!D$8:D$400,'Month (2)'!B$8:B$400,C8)+SUMIFS('Month (2)'!G$8:G$400,'Month (2)'!E$8:E$400,C8)


All the way to Sheet Month (12)


+SUMIFS('Month (12)'!D$8:D$400,'Month (12)'!B$8:B$400,C8)+SUMIFS('Month (12)'!G$8:G$400,'Month (12)'!E$8:E$400,C8)



I tried to use shortened formula below by using a use a range of sheets but this does not work and returns a VALUE error

=IF(C8="","",SUMIFS('Month (1):Month (12)'!D8:D400,'Month (1):Month (12)'!B8:B400,C8))

 

[Yongle]

Re: Help with VALUE error when using a range of sheets in formula

Have a look at https://exceljet.net/formula/3d-sumif-for-multiple-worksheets

Another way to approach this would be to put the 12 (sheet specific elements of SUMIFS) formulas in the same cell in each sheet (eg A3) and then a simple sum across sheets
=SUM('Month (1):Month (12)'!A3)

 

[Fazza]

Re: Help with VALUE error when using a range of sheets in formula

if other approaches are of interest, and it suits, put all the data on one sheet. work more like a database. much simpler formulas
or have a pivot table or query table and work without any formulas.