Hi everybody,

First of all I wish you all a very Happy and Prosperous new year
2006.

I have 10 work sheets, in the 11th sheet I want to sum a column based
on the column F value. I'm givivg you an example for this.

In 11th work sheet in column F I have this formula.

=sumif('sheet1!'D1:D100="X",'sheet1!'f1:f100)+sumi f('sheet2!'D1:D100="X",'sheet2!'f1:f100)+sumif('sh eet3!'D1:D100="X",'sheet3!'f1:f100)+sumif('sheet4! 'D1:D100="X",'sheet4!'f1:f100)+.....sumif('sheet10 !'D1:D100="X",'sheet10!'f1:f100).
Some times the sheets may go up to 50.

So I need a nested formula like
sumif(Sheet1:sheet10d1:d100="x",sheet1:sheet10f1:f 100)

If any body hae suggestions plz give me.

Thanks and Regards

Ramana

Take a look he

http://www.mcgimpsey.com/excel/threedsumif.html

In article . com,
"ramana" wrote:

Hi everybody,

First of all I wish you all a very Happy and Prosperous new year
2006.

I have 10 work sheets, in the 11th sheet I want to sum a column based
on the column F value. I'm givivg you an example for this.

In 11th work sheet in column F I have this formula.

=sumif('sheet1!'D1:D100="X",'sheet1!'f1:f100)+sumi f('sheet2!'D1:D100="X",'shee
t2!'f1:f100)+sumif('sheet3!'D1:D100="X",'sheet3!'f 1:f100)+sumif('sheet4!'D1:D1
00="X",'sheet4!'f1:f100)+.....sumif('sheet10!'D1:D 100="X",'sheet10!'f1:f100).
Some times the sheets may go up to 50.

So I need a nested formula like
sumif(Sheet1:sheet10d1:d100="x",sheet1:sheet10f1:f 100)

If any body hae suggestions plz give me.

Thanks and Regards

Ramana

Ramana,

Put your sheet names in a range, I use C1:C!0 in the example, you would need
a bigger range for 50, and use

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C10&"'!D1:D100") ,"X",INDIRECT("'"&C1:C10&"
'!F1:F100")))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ramana" wrote in message
ups.com...
Hi everybody,

First of all I wish you all a very Happy and Prosperous new year
2006.

I have 10 work sheets, in the 11th sheet I want to sum a column based
on the column F value. I'm givivg you an example for this.

In 11th work sheet in column F I have this formula.


=sumif('sheet1!'D1:D100="X",'sheet1!'f1:f100)+sumi f('sheet2!'D1:D100="X",'sh
eet2!'f1:f100)+sumif('sheet3!'D1:D100="X",'sheet3! 'f1:f100)+sumif('sheet4!'D
1:D100="X",'sheet4!'f1:f100)+.....sumif('sheet10!' D1:D100="X",'sheet10!'f1:f
100).
Some times the sheets may go up to 50.

So I need a nested formula like
sumif(Sheet1:sheet10d1:d100="x",sheet1:sheet10f1:f 100)

If any body hae suggestions plz give me.

Thanks and Regards

Ramana