SUMIF MULTIPLE SHEETS

[MikeMN]

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

 

[Aladin Akyurek]

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

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.

 

[MikeMN]

Re: SUMIF MULTIPLE SHEETS Follow up

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

 

[Aladin Akyurek]

Re: SUMIF MULTIPLE SHEETS Follow up

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

Free ASAP Utilities allows you to construct a sheet index, which you can probably use:

http://asap-utilities.com

 

[roscoe77]

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.

 

[Aladin Akyurek]

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.

Did you also try the manual method of constructing the sheet list?

 

[alexcn]

I am thinking the SUMPRODUCT(SUMIF(..)) method only works with a single criteria, yes?

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

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.

 

[jindon]

Re: SUMIF MULTIPLE SHEETS Follow up

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

Place the code onto Standard module.

Select A1:A100 and enter
=GetWsNames(1,100)
and confirm with Ctrl + Shift + Enter

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]

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

 

[Aladin Akyurek]

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

Why not INDIRECT? Care to elaborate?