summing across worksheets conditionally and being able to copy for
 

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.

In addition. there are more than one cell that I am summing. The solution
that someone provided me below worked great. So I tried copying the cell to
grab the other data that I needed but the cell B2 which in his example is the
cell I am grabbing in each worksheet is not dynamic so when you copy the cell
with the formula it stays B2. I tried changing it to pull the B2 out of the
parenthesis but it didn't work I got a circular reference. Any suggestions.
I really need to be able to copy the formula down 30 rows and across 30
columns.

This is what I tried:
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))



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

--
Celia

summing across worksheets conditionally and being able to copy for
 

I really need to be able to copy the formula
down 30 rows and across 30 columns.
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))

Does that mean when you drag copy the formula down then B2 should become:

B2
B3
B4
B5
etc

And when you drag copy the formula across a row then B2 should become:

B2, C2, D2, E2, etc

--
Biff
Microsoft Excel MVP


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

In addition. there are more than one cell that I am summing. The solution
that someone provided me below worked great. So I tried copying the cell
to
grab the other data that I needed but the cell B2 which in his example is
the
cell I am grabbing in each worksheet is not dynamic so when you copy the
cell
with the formula it stays B2. I tried changing it to pull the B2 out of
the
parenthesis but it didn't work I got a circular reference. Any
suggestions.
I really need to be able to copy the formula down 30 rows and across 30
columns.

This is what I tried:
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))



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

--
Celia

summing across worksheets conditionally and being able to copy
 

--
Celia


"T. Valko" wrote:

I really need to be able to copy the formula
down 30 rows and across 30 columns.
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))

Does that mean when you drag copy the formula down then B2 should become:

B2
B3
B4
B5
etc

And when you drag copy the formula across a row then B2 should become:

B2, C2, D2, E2, etc

--
Biff
Microsoft Excel MVP


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

In addition. there are more than one cell that I am summing. The solution
that someone provided me below worked great. So I tried copying the cell
to
grab the other data that I needed but the cell B2 which in his example is
the
cell I am grabbing in each worksheet is not dynamic so when you copy the
cell
with the formula it stays B2. I tried changing it to pull the B2 out of
the
parenthesis but it didn't work I got a circular reference. Any
suggestions.
I really need to be able to copy the formula down 30 rows and across 30
columns.

This is what I tried:
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))



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

--
Celia




That is exactly what I mean. Why doesn't it work taking the B2 out of the
parenthesis? Does it not make it dynamic that way? Since I was getting a
circular reference it appeared to me that it was trying to make it the B2 in
the new worksheet that I was doing the calculation in not the worksheets I
was trying to sum B2 in.

Celia

summing across worksheets conditionally and being able to copy
 

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

The way that that formula is written it's not referencing cell B2 on each of
the sheets. It's referencing cell B2 on the sheet that the formula is
entered in.

If you want to reference cell B2 on each of the sheets then:

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

However, as written, that won't allow the reference to B2 increment as you
drag copy.

What is the very first cell where you want to enter that formula?

--
Biff
Microsoft Excel MVP


"Celia" wrote in message
...

--
Celia


"T. Valko" wrote:

I really need to be able to copy the formula
down 30 rows and across 30 columns.
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))

Does that mean when you drag copy the formula down then B2 should become:

B2
B3
B4
B5
etc

And when you drag copy the formula across a row then B2 should become:

B2, C2, D2, E2, etc

--
Biff
Microsoft Excel MVP


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

In addition. there are more than one cell that I am summing. The
solution
that someone provided me below worked great. So I tried copying the
cell
to
grab the other data that I needed but the cell B2 which in his example
is
the
cell I am grabbing in each worksheet is not dynamic so when you copy
the
cell
with the formula it stays B2. I tried changing it to pull the B2 out
of
the
parenthesis but it didn't work I got a circular reference. Any
suggestions.
I really need to be able to copy the formula down 30 rows and across 30
columns.

This is what I tried:
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))



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

--
Celia




That is exactly what I mean. Why doesn't it work taking the B2 out of the
parenthesis? Does it not make it dynamic that way? Since I was getting a
circular reference it appeared to me that it was trying to make it the B2
in
the new worksheet that I was doing the calculation in not the worksheets I
was trying to sum B2 in.

Celia

summing across worksheets conditionally and being able to copyfor
 

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

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

JB

On 18 mar, 23:15, 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. *

In addition. there are more than one cell that I am summing. *The solution
that someone provided me below worked great. *So I tried copying the cell to
grab the other data that I needed but the cell B2 which in his example is the
cell I am grabbing in each worksheet is not dynamic so when you copy the cell
with the formula it stays B2. *I tried changing it to pull the B2 out of the
parenthesis but it didn't work I got a circular reference. *Any suggestions. *
*I really need to be able to copy the formula down 30 rows and across 30
columns.

This is what I tried:
*=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))

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

--
Celia