sumifs to loop all sheets

Question

I have been searching different forums and cant seem to find my answer. I have rather basic VBA knowledge and build most of my code from bits online!

Regardless of cell references as I would be able to work these out at a later date. Please can you let me how I would make a sumifs formula reference across multiple sheets. This is being build into a template and there would be a different number of sheets with different names each time it is run so I would be not be able to reference the sheets.

sorry thats a bit vague thanks in advanced

You could use Sheets(1) or whatever the sheet number reference is in your workbook. There are a lot of things you could potentially do, but hard to say without more information. — Histerical
View the answer to this question for an idea of how to reference sheets by number within a formula: stackoverflow.com/questions/19790985/… — David
i would basicly need in my summary sheet in cell B3 to do a sumifs(B2006:B3005(over all sheets), where summaryA3 = all sheets A2006:A3005 and summary B2 = all sheets B2005) — Fcarboni
Thanks David, I dont think I can use this as the workbook could have 3 sheets or 300 sheets, this is unknown un till the first macro is run so cant reference sheet numbers, also if it is run once, then again, it creates new sheets and deletes the old ones which shifts on the sheet numbers, thatsthe main reason I need a loop for all sheets in the workbook! — Fcarboni
Google "3d SUMIFS". It will show how to use a formula for this. — Scott Craner

Fcarboni Fcarboni · Accepted Answer · 2016-05-20T14:21:05

Thanks, so for anyone else who needs this, this is how it was done in full my original formula was

"=SUMPRODUCT(SUMIF(INDIRECT(" '"&Invoices&"'!"&"A2006:A3005"),A3,INDIRECT("'"&Invoices&"'!"&"B2006:B3005")))"

this worked when putting straight into a cell but as you can see, when adding it to VBA it reads it as a comment. To fix this, every time you use a " you need to add extra " as shown bellow (apart form before the" = at the start and after the )" at the end of the formula)

 *****'list all the sheet names in cell AI1 of the sheet summary*****
For i = 1 To Sheets.Count
Sheets("Summary").Range("AI1")(i, 1).Value = Sheets(i).Name
Next i
***'clear the first 3 entries in AI as i didnt need the first three sheet names***
Sheets("Summary").Select
Sheets("Summary").Range("AI1:AI3").Clear
***'select the first sheet name, which is in AI4 as we cleard the first 3 to the last used cell, e.g Ctrl.Shift.down*** 
Sheets("Summary").Activate
Sheets("summary").Range(ActiveSheet.Range("AI4"),    ActiveSheet.Range("AI4").End(xlDown)).Select
***' Name the range invoices***
Selection.Name = "Invoices"
' ***Formula to do a sumIf looping all the shets in the named range Invoices***
Sheets("summary").Range("B3").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&Invoices&""'!$A$2006:$A$3005""),$A3,INDIRECT(""'""&Invoices&""'!B$2006:B$3005"")))"

sumifs to loop all sheets

1 Answers