Excel Conditional Sum
 

To Whom it may concern:

I am working with data that I learned in the Nursing Reportiing webcast last
week.

When I create the table & go to copy the formula & up the month by one the
data does not update. Below are the codes one copied & one created when using
the conditional sum wizard:

=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))
=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))

Any thoughts why the data doesn't update & only returns the value of 0?
--
Thanks,
Andy

Excel Conditional Sum
 

It seems like you have a couple of issues with this formula:

=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))

First, to answer your initial question, I believe that is an array formula
and must be commited by holding down [Ctrl][Shift] when you press [Enter],
instead of just pressing [Enter].
(You'll know you did it right if Excel puts braces { } around your
formula....you can't type them in yourself)

Second, this non-array formula is probably easier to work with:

=SUMPRODUCT(('PNB Raw'!$A$2:$A$253="PN3b")*('PNB Raw'!$C$2:$C$253=4)*('PNB
Raw'!$D$2:$D$253=2005)*('PNB Raw'!$H$2:$H$253))

Note_1: You can commit that formula by just pressing [Enter].
Note_2: In case of text wrapping, there are no spaces in that formula.

Post back if you have questions.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Andy" wrote:

To Whom it may concern:

I am working with data that I learned in the Nursing Reportiing webcast last
week.

When I create the table & go to copy the formula & up the month by one the
data does not update. Below are the codes one copied & one created when using
the conditional sum wizard:

=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))
=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))

Any thoughts why the data doesn't update & only returns the value of 0?
--
Thanks,
Andy

Excel Conditional Sum
 

Ron,

Thank you very much, as I am learning a ton from this discussion website.
THANK GOODNESS!!!

Will this also help the 255 issues with the Arrays?

--
Thanks,
Andy


"Ron Coderre" wrote:

It seems like you have a couple of issues with this formula:

=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))

First, to answer your initial question, I believe that is an array formula
and must be commited by holding down [Ctrl][Shift] when you press [Enter],
instead of just pressing [Enter].
(You'll know you did it right if Excel puts braces { } around your
formula....you can't type them in yourself)

Second, this non-array formula is probably easier to work with:

=SUMPRODUCT(('PNB Raw'!$A$2:$A$253="PN3b")*('PNB Raw'!$C$2:$C$253=4)*('PNB
Raw'!$D$2:$D$253=2005)*('PNB Raw'!$H$2:$H$253))

Note_1: You can commit that formula by just pressing [Enter].
Note_2: In case of text wrapping, there are no spaces in that formula.

Post back if you have questions.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Andy" wrote:

To Whom it may concern:

I am working with data that I learned in the Nursing Reportiing webcast last
week.

When I create the table & go to copy the formula & up the month by one the
data does not update. Below are the codes one copied & one created when using
the conditional sum wizard:

=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))
=SUM(IF('PNB Raw'!$A$2:$A$253="PN3b",IF('PNB Raw'!$C$2:$C$253=4,IF('PNB
Raw'!$D$2:$D$253=2005,'PNB Raw'!$H$2:$H$253,0),0),0))

Any thoughts why the data doesn't update & only returns the value of 0?
--
Thanks,
Andy