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!

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 #1:PD #3'!A2:A100)=A8),THREED('PD #1:PD
#3'!F2:F100))

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

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

Hope this helps!

In article . com,
wrote:

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!

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 wrote:
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 #1:PD #3'!A2:A100)=A8),THREED('PD #1:PD
#3'!F2:F100))

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

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

Hope this helps!

In article . com,
wrote:

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!

In article .com,
wrote:

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?

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

"Domenic" wrote:

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 #1:PD #3'!A2:A100)=A8),THREED('PD #1:PD
#3'!F2:F100))

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

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

Hope this helps!

In article . com,
wrote:

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!