sum across worksheets conditionally
 

I have about 50+ worksheets in a file that I need to sum. However, I only
want to sum some of the sheets if it meets a certain criteria. This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. If a
worksheet is of a certain type I need the sum of those types.


Celia

sum across worksheets conditionally
 

http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls

Named range
cond $H$2:$H$8
nf $G$2:$G$8
nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&""))

=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0"))

JB
http://boisgontierjacques.free.fr/

On 18 mar, 03:17, Celia wrote:
I have about 50+ worksheets in a file that *I need to sum. *However, I only
want to sum some of the sheets if it meets a certain criteria. *This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. *If a
worksheet is of a certain type I need the sum of those types.

Celia

sum across worksheets conditionally
 

or

=SumProduct(N(INDIRECT(nfCond&"!B2")))

JB

On 18 mar, 07:18, JB wrote:
http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls

Named range
cond * *$H$2:$H$8
nf * * *$G$2:$G$8
nfCond *=OffSet($J$2,,,CountIf($J$2:$J$12,"<"&""))

=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0"))

JBhttp://boisgontierjacques.free.fr/

On 18 mar, 03:17, Celia wrote:



I have about 50+ worksheets in a file that *I need to sum. *However, I only
want to sum some of the sheets if it meets a certain criteria. *This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. *If a
worksheet is of a certain type I need the sum of those types.

Celia- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

sum across worksheets conditionally
 

Thank you so much. I am not sure if I completely understands how or why it
works but I really appreciate your help.


--
Celia


"JB" wrote:


http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls

Named range
cond $H$2:$H$8
nf $G$2:$G$8
nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&""))

=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0"))

JB
http://boisgontierjacques.free.fr/

On 18 mar, 03:17, Celia wrote:
I have about 50+ worksheets in a file that I need to sum. However, I only
want to sum some of the sheets if it meets a certain criteria. This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. If a
worksheet is of a certain type I need the sum of those types.

Celia

.

sum across worksheets conditionally
 

I used the formula and it worked for one cell in the worksheet. THere is a
whole bunch of data that I want to sum in each of the worksheets. How do I
get the Cell B2 in your example to auto change as I copy the formula ex. so
that I can sum b3, b4 ,b5, ....
--
Celia


"JB" wrote:


http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls

Named range
cond $H$2:$H$8
nf $G$2:$G$8
nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&""))

=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0"))

JB
http://boisgontierjacques.free.fr/

On 18 mar, 03:17, Celia wrote:
I have about 50+ worksheets in a file that I need to sum. However, I only
want to sum some of the sheets if it meets a certain criteria. This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. If a
worksheet is of a certain type I need the sum of those types.

Celia

.

sum across worksheets conditionally
 

Formula in B2:
=SumProduct(N(Offset(INDIRECT(nfCond&"!B2"),Row()-2,Column()-2)))

http://boisgontierjacques.free.fr/fi...Sum3DCond2.xls

JB


On 18 mar, 20:10, Celia wrote:
I used the formula and it worked for one cell in the worksheet. *THere is a
whole bunch of data that I want to sum in each of the worksheets. *How do I
get the Cell B2 in your example to auto change as I copy the formula ex. so
that I can sum b3, b4 ,b5, ....
--
Celia



"JB" wrote:

http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls

Named range
cond * * * $H$2:$H$8
nf $G$2:$G$8
nfCond * * =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&""))

=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0"))

JB
http://boisgontierjacques.free.fr/

On 18 mar, 03:17, Celia wrote:
I have about 50+ worksheets in a file that *I need to sum. *However, I only
want to sum some of the sheets if it meets a certain criteria. *This criteria
is located in a separate worksheet in the file that lists each worksheet name
in one column and in the next column it list the type of worksheet. *If a
worksheet is of a certain type I need the sum of those types.

Celia

.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -