sumif accross multiple worksheets

[Ricky Morris]

Sumif doesn't seems to work accross multiple sheets. How do I accomplish the same results either with an array formula or vba. Excel doesn't seems to recognize a range accross multiple sheets as a valid reference: =ISREF(Sheet2:Sheet3!B10) returns a false. I get the same result when I try to pass the following into a custom function:

Cell A1 = mysum(Sheet2:Sheet3!B10), where mysum is:

Public Function mysum(v As Variant)
Application.Volatile
test = WorksheetFunction.Sum(v)
End Function

 

[Ricky Morris]

Yogi, it seems like your solution should work for me but I'm a little slow tonight.

Assuming I have 3 sheets with data named A, B and C. On a fourth sheet named Total I want to evaluate the contents of cell A1 of the 3 data sheets and if A1 is = YES then take to sum of cell A2 on that sheet. How would I write the formula you noted to accomplish this?

 

[Yogi Anand]

Hi Ricky:

Here we go -- sheet1...

y030811h2.xls
	A	B	C	D
1	Yes
2	1
Sheet1

sheet2 ...

y030811h2.xls
	A	B	C	D
1	No
2	2
Sheet2

sheet3 ...

y030811h2.xls
	A	B	C	D
1	Yes
2	3
Sheet3

and Totals ...

y030811h2.xls
	A	B	C	D
1	4
Totals

The formula in cell A1 is ...

{=SUM(SUMIF(INDIRECT("sheet"&ROW(1:3)&"!A1"),"Yes",INDIRECT("sheet"&ROW(1:3)&"!A2")))}

Would this do?

 

[PaddyD]

also, if you've got sheets named "A", "B", "C":

=SUM(SUMIF(INDIRECT(CHAR({65;66;67})&"!A1"),"Yes",INDIRECT(CHAR({65;66;67})&"!A2")))

 

[Ricky Morris]

Yes, that works perfectly. I'm still trying to comprehend how it works but for now I'm happy I found a solution. As usual I can always find a solution here.

Thanks, Yogi.

 

[Yogi Anand]

also, if you've got sheets named "A", "B", "C":

=SUM(SUMIF(INDIRECT(CHAR({65;66;67})&"!A1"),"Yes",INDIRECT(CHAR({65;66;67})&"!A2")))

Ah! the Sheets are A, B, and C instead of Sheet1, Sheet2, and Sheet3 -- Thanks -- Paddy.

How about just using ...

=SUM(SUMIF(INDIRECT({"A","B","C"}&"!A1"),"Yes",INDIRECT({"A","B","C"}&"!A2")))

and in this case it does not even need to be an array based formula!

 

[PaddyD]

Yeh - but it's not half as opaque!