SUMIF across multiple worksheets workaround help
 

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!

SUMIF across multiple worksheets workaround help
 

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!

SUMIF across multiple worksheets workaround help
 

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!

SUMIF across multiple worksheets workaround help
 

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?

SUMIF across multiple worksheets workaround help
 

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!