--
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