Formula to get sum from one 31 sheets to a summary sheet.

[Eric]

I have figured out the formula to get the sum from 31 sheets on to a summary
sheet at the end of the Excel book; EX: =SUM(1:31!J3:J8). However, I now need
to know how to do it with an exception. Ex: I need =SUM(1:31!P34) BUT ONLY
IF J3:J4 IS POPULATED + (1:31!P56) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P78) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in to one
formula.

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!J3:J6"),"<>",INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!P36")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Eric]

Did not work. I am sure I am doing something wrong.

 

[Bob Phillips]

Did not work? returned an error? Returned nothing? Blew up your computer?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Eric]

Hi Bob,

What I am trying to accomplish is this: get the sum of positive numbers from
pages "1" to "31" / Cells P3 to P8 to show up in sheet "Monthly Summary" /
Cell F5 and the sum of negative numbers from pages "1" to "31" / Cells P3 to
P8 to show up in sheet "Monthly Summary" / Cell F8.

I have tried to enter the formula just as you typed it and I get an error
message telling me it is an incorrect formula.

This is brand new to me, please forgive me if my related communication is
not clear. I would appreciate any help you can offer to get me through this.

Eric

 

[Bob Phillips]

It is easily adapted to the se requirements Eric.

I have posted an example at http://cjoint.com/?gyavXK3ZE3. I have only put
some values in sheets 1 and 3 but it shows the principle.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Eric]

Thank you for sending the link for the example. I actually copied and pasted
it right into my workbook and it work great! The only problem...I made a
mistake in that I left something out.

In all actuallity, it needs to be as follows:
F5 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P34*D34 (IF POSSITIVE) + P56*D56 (IF POSITIVE) + P78*D78 (IF
POSSITIVE)
F8 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P34*D34 (IF NEGATIVE) + P56*D56 (IF NEGATIVE) + P78*D78 (IF
NEGATIVE)

I apologize for the mistake. If you can send a link to a book with the
formulas already in it again it would be greatly appreciated.

 

[Eric]

Thank you for sending the link for the example. I actually copied and pasted
it right into my workbook and it work great! The only problem...I made a
mistake in that I left something out.

In all actuallity, it needs to be as follows:
F5 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P34*D34 (IF POSSITIVE) + P56*D56 (IF POSITIVE) + P78*D78 (IF
POSSITIVE)
F8 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P34*D34 (IF NEGATIVE) + P56*D56 (IF NEGATIVE) + P78*D78 (IF
NEGATIVE)

I apologize for the mistake. If you can send a link to a book with the
formulas already in it again it would be greatly appreciated.