RE: Excel Conditional Sum

Giganews Newsgroups
Subject: RE: Excel Conditional Sum
Posted by:  Ron Coderre
Date: Mon, 1 May 2006

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

Replies

In response to

Excel Conditional Sum posted by Andy on Mon, 1 May 2006