software

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=quot;Xquot;,'sheet1!'f1:f100) sumi f('sheet2!'D1100=quot;Xquot;,'sheet2!'f1:f100) sumif('sh eet3!'D1100=quot;Xquot;,'sheet3!'f1:f100) sumif('sheet4! 'D1100=quot;Xquot;,'sheet4!'f1:f100) .....sumif('sheet10 !'D1100=quot;Xquot;,'sheet10!'f1:f100).
Some times the sheets may go up to 50.

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

If any body hae suggestions plz give me.

Thanks and Regards

RamanaTake a look he

www.mcgimpsey.com/excel/threedsumif.html

In article . comgt;,
quot;ramanaquot; gt; wrote:

gt; Hi everybody,
gt;
gt; First of all I wish you all a very Happy and Prosperous new year
gt; 2006.
gt;
gt; I have 10 work sheets, in the 11th sheet I want to sum a column based
gt; on the column F value. I'm givivg you an example for this.
gt;
gt; In 11th work sheet in column F I have this formula.
gt;
gt; =sumif('sheet1!'D1100=quot;Xquot;,'sheet1!'f1:f100) sumi f('sheet2!'D1100=quot;Xquot;,'shee
gt; t2!'f1:f100) sumif('sheet3!'D1100=quot;Xquot;,'sheet3!'f 1:f100) sumif('sheet4!'D11
gt; 00=quot;Xquot;,'sheet4!'f1:f100) .....sumif('sheet10!'D1 100=quot;Xquot;,'sheet10!'f1:f100).
gt; Some times the sheets may go up to 50.
gt;
gt; So I need a nested formula like
gt; sumif(Sheet1:sheet10d1:d100=quot;xquot;,sheet1:sheet10f1:f 100)
gt;
gt; If any body hae suggestions plz give me.
gt;
gt; Thanks and Regards
gt;
gt; 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(quot;'quot;amp;C1:C10amp;quot;'!D1100quot;) ,quot;Xquot;,INDIRECT(quot;'quot;amp;C1:C10amp;quot;
'!F1:F100quot;)))

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;ramanaquot; gt; wrote in message ups.com...
gt; Hi everybody,
gt;
gt; First of all I wish you all a very Happy and Prosperous new year
gt; 2006.
gt;
gt; I have 10 work sheets, in the 11th sheet I want to sum a column based
gt; on the column F value. I'm givivg you an example for this.
gt;
gt; In 11th work sheet in column F I have this formula.
gt;
gt;
=sumif('sheet1!'D1100=quot;Xquot;,'sheet1!'f1:f100) sumi f('sheet2!'D1100=quot;Xquot;,'sh
eet2!'f1:f100) sumif('sheet3!'D1100=quot;Xquot;,'sheet3! 'f1:f100) sumif('sheet4!'D
1100=quot;Xquot;,'sheet4!'f1:f100) .....sumif('sheet10!' D1100=quot;Xquot;,'sheet10!'f1:f
100).
gt; Some times the sheets may go up to 50.
gt;
gt; So I need a nested formula like
gt; sumif(Sheet1:sheet10d1:d100=quot;xquot;,sheet1:sheet10f1:f 100)
gt;
gt; If any body hae suggestions plz give me.
gt;
gt; Thanks and Regards
gt;
gt; Ramana
gt;