summing across worksheets conditionally and being able to copy for

[Celia]

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/fichiers/Matriciel/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/

 

[T. Valko]

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

 

[Celia]

--
Celia

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

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

 

[T. Valko]

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

 

[JB]

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

http://boisgontierjacques.free.fr/fichiers/Matriciel/Sum3DCond2.xls

JB