Sumif across mutiple sheets
 

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 he
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",I NDIRECT("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!

Sumif across mutiple sheets
 

Put the sheet names in M1:M20 and use

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

--
---
HTH

Bob


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



"jwang036" wrote in message
...
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 he
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",I NDIRECT("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!

Sumif across mutiple sheets
 

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" wrote:

Put the sheet names in M1:M20 and use

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

--
---
HTH

Bob


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



"jwang036" wrote in message
...
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 he
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",I NDIRECT("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!

Sumif across mutiple sheets
 

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)



"jwang036" wrote in message
...
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" wrote:

Put the sheet names in M1:M20 and use

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

--
---
HTH

Bob


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



"jwang036" wrote in message
...
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 he
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",I NDIRECT("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!