Nesting of sumif formula.

[ramana]

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!'D1100="X",'sheet1!'f1:f100)+sumif('sheet2!'D1100="X",'sheet2!'f1:f100)+sumif('sheet3!'D1100="X",'sheet3!'f1:f100)+sumif('sheet4!'D1100="X",'sheet4!'f1:f100)+.....sumif('sheet10!'D1100="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:f100)

If any body hae suggestions plz give me.

Thanks and Regards

Ramana

 

[JE McGimpsey]

Take a look here:

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

 

[Bob Phillips]

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&"'!D1100"),"X",INDIRECT("'"&C1:C10&"
'!F1:F100")))

--

HTH

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