SUMIF across multiple worksheets workaround help

[grant.henninger]

What I would like to do is a SUMIF from sheet First to Last and
everything in between, but I know that isn't possible, so I've been
working on a workaround. This is what I have so far that gives me a
#VALUE! error:

=SUMPRODUCT(SUMIF(INDIRECT("'PD
#"&ROW(INDIRECT("1:2"))&"'!A:A"),A8,INDIRECT("'PD
#"&ROW(INDIRECT("1:2"))&"'!F:F")))

My sheets are named PD #1, PD #2, PD #3, etc. (Right now I'm just
trying to sum the first two sheets to see if it works.) If the value
in column A on sheet PD #whatever for any given row equals the value in
A8 then sum the values in column F of sheet PD #whatever.

If anybody has any help it would be greatly appreciated. I'm on
Windows XP and using Excel 2003.

Thank you!

 

[Domenic]

The syntax for the formula is correct. Although, when I copied and
pasted the formula into a worksheet I found two hard returns. Remove
them, and make sure a space exists between PD and #, and you should be
okay.

Also, if you download and install the free add-in Morefunc.xll, you can
use the following formula...

=SUMPRODUCT(--(THREED('PD #1D #3'!A2:A100)=A8),THREED('PD #1D
#3'!F2:F100))

The add-in can be download at the following link...

http://xcell05.free.fr/english/index.html

Hope this helps!

 

[grant.henninger]

Will the formula then only work on computers that have that add-in
installed? Because I need it to work on other people's computers as
well.

I think the hard returns are a result of pasting the formula into
Google, because I didn't type it in that way. And there are spaces
between PD and # both in the formula and in the worksheet names. I'm
still getting an error.

Thanks for letting me know about the add-in.

Grant

 

[Domenic]

Will the formula then only work on computers that have that add-in
installed? Because I need it to work on other people's computers as
well.

There's no need for other computers to install the add-in. With the
latest version, you can embed it in the file...

Tools > Morefunc > Include Morefunc

I think the hard returns are a result of pasting the formula into
Google, because I didn't type it in that way. And there are spaces
between PD and # both in the formula and in the worksheet names. I'm
still getting an error.

Would you like me to send you a sample file?

 

[Guest]

Domenic, This solution seemed to be the answer I was looking for, for similar
problem. I down loaded the add in and tried the formula. However I got a
#REF! error message. Can you maybe explain this to me?

Tracy

 

[Guest]

Domenic, This solution seemed to be the answer I was looking for, for similar
problem. I down loaded the add in and tried the formula. However I got a
#REF! error message. Can you maybe explain this to me?

Tracy