Sumif across mutiple sheets

[jwang036]

I almost found something here, but not exactly what I need.

I have 20 sheets named (tabbed) after different cities (let's say starts
with "SHA", ends with "SYD"). Their row 10 (from column F to Z) contains when
the sales happens (2008,2009 etc, BUT each has a different start). Row 12
(from column F to Z) has the corresponding sales amount.

I also have a total sheet and in the total sheet I need to sum up the sales
of all the cities by year (from column F=2008 to column Z=2028).

I find a fomula here:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",INDIRECT("Sheet"&{1,2,3}&"!C:C"))).
The only problem is I have 20 sheets and I tried to use "SHA:SYD!F10:Z10" to
replace "Sheet"&{1,2,3}&"!C:C" but it doesn't work. Any idea? Thanks!

 

[Bob Phillips]

Put the sheet names in M1:M20 and use

=SUMPRODUCT(SUMIF(INDIRECT(M1:M20&"!A:A"),"EU",INDIRECT(M1:M20&"!C:C")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jwang036]

Thanks a lot! Just for my curiosity, why sumproduct? I tried sum, it didn't
work. I didn't think of using sumproduct at all.

 

[Bob Phillips]

SUM should work if array-entered, but my approach was different to yours.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)