SUMIF MULTIPLE SHEETS

MikeMN said:

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

Click to expand...

MikeMN said:

THANK YOU

I tested Method 1 and it works great.

However,

I have over 100 Sheets in the file, is there a way to get a list of all the sheet names. Then I can name the list as you suggusted.

Mike

Click to expand...

roscoe77 said:

Thanks for your input!

For whatever reason, I am not getting the formula to work (I literally copy and pasted your formula in excel and it doesn't work so I am not sure if I am naming my list proberly or what. Used the ASAP module and named the range SheetList. I get a REF# error.

Click to expand...

MikeMN said:

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

Click to expand...

Two options...

1]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion.

Click to expand...

THANK YOU

I tested Method 1 and it works great.

However,

I have over 100 Sheets in the file, is there a way to get a list of all the sheet names. Then I can name the list as you suggusted.

Mike

Click to expand...

Function GetWsNames(StartInd As Long, EndInd As Long)
Dim i As Long, a(), x As Long, n As Long
Application.Valatile
If EndInd > Sheets.Count Then EndInd = Sheets.Count
x = EndInd - StartInd + 1
Redim a(1 To x)
For i = StartInd To EndInd
    n = n + 1
    a(n) = Sheets(i).Name
Next
GetWsNames = Application.Transpose(a)
End Function

ugh said:

Hi! Is there any other option other than using the indirect funtion? Any VLOOKUP option?

Click to expand...