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

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!P3:P4) BUT ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in to one
formula.

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

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4) BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in to
one
formula.

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

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

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4) BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in to
one
formula.

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

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4) BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in
to
one
formula.

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

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" wrote:

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4) BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED +
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all in
to
one
formula.

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

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" wrote in message
...
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" wrote:

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4)
BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED
+
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all
in
to
one
formula.

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

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"
P3:P4*D3:D4 (IF POSSITIVE) + P5:P6*D5:D6 (IF POSITIVE) + P7:P8*D7:D8 (IF
POSSITIVE)
F8 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P3:P4*D3:D4 (IF NEGATIVE) + P5:P6*D5:D6 (IF NEGATIVE) + P7:P8*D7:D8 (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.

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4)
BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED
+
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all
in
to
one
formula.

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

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"
P3:P4*D3:D4 (IF POSSITIVE) + P5:P6*D5:D6 (IF POSITIVE) + P7:P8*D7:D8 (IF
POSSITIVE)
F8 of sheet "Monthly Summary" is to be the sum of sheets "1" to "31"
P3:P4*D3:D4 (IF NEGATIVE) + P5:P6*D5:D6 (IF NEGATIVE) + P7:P8*D7:D8 (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.

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4)
BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED
+
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all
in
to
one
formula.











"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
Did not work. I am sure I am doing something wrong.

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"Eric" wrote in message
...
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!P3:P4)
BUT
ONLY
IF J3:J4 IS POPULATED + (1:31!P5:P6) BUT ONLY IF J5:J6 IS POPULATED
+
(1:31!P7:P8) BUT ONLY IF J7:J8 IS POPULATED. I need to put this all
in
to
one
formula.