Problem with a Conditional Sum

[lbarrett]

I have two columns, M (Expenditures) and AB (a yes/no column). I need to sum the values in M if the corresponding cell in AB is a "yes" -- so for building 2, if AB2 is a yes, M2 is included in the sum. So far, simple enough: {SUM(IF('Historic Bldgs'!$AB$1:$AB$281="yes",'Historic Bldgs'!$M$1:$M$281))}

But there's one other issue: some of the cells in M contain #N/A's. I have worked around this when doing a simple sum of column M with the formula SUMIF('Historic Bldgs'!M1:M281,"<>#N/A") -- so I assumed tacking on the last part would work with the conditional sum, i.e. {SUM(IF('Historic Bldgs'!$AB$1:$AB$281="yes",'Historic Bldgs'!$M$1:$M$281, "<>#N/A"))} but it doesn't. What's wrong with my formula?

 

[Aladin Akyurek]

On 2002-10-30 13:56, lbarrett wrote:
I have two columns, M (Expenditures) and AB (a yes/no column). I need to sum the values in M if the corresponding cell in AB is a "yes" -- so for building 2, if AB2 is a yes, M2 is included in the sum. So far, simple enough: {SUM(IF('Historic Bldgs'!$AB$1:$AB$281="yes",'Historic Bldgs'!$M$1:$M$281))}

But there's one other issue: some of the cells in M contain #N/A's. I have worked around this when doing a simple sum of column M with the formula SUMIF('Historic Bldgs'!M1:M281,"<>#N/A") -- so I assumed tacking on the last part would work with the conditional sum, i.e. {SUM(IF('Historic Bldgs'!$AB$1:$AB$281="yes",'Historic Bldgs'!$M$1:$M$281, "<>#N/A"))} but it doesn't. What's wrong with my formula?

Either

eliminate #N/A's from your data & use a simple & efficient SUMIF formula

Or array-enter...

=SUM(IF(ISNUMBER('Historic Bldgs'!$M$1:$M$281), ('Historic Bldgs'!$AB$1:$AB$281="yes")*('Historic Bldgs'!$M$1:$M$281)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

 

[lbarrett]

Eliminating the #N/A's is unfortunately not an option (they're lookups, and the source data may not be finalized) . . . but your array formula does the job. Thank you!!

 

[IML]

On 2002-10-30 14:38, lbarrett wrote:
Eliminating the #N/A's is unfortunately not an option (they're lookups, and the source data may not be finalized) . . . but your array formula does the job. Thank you!!

Just to give you something to play with, if your lookup against incomplete data is
=VLOOKUP(A1,mylist,2,0)

you could eliminate the n/a's with
=IF(ISNUMBER(MATCH(A1,INDEX(mylist,0,1),0)),VLOOKUP(A1,mylist,2,0),"")

As you expand mylist, new items will be considered where applicable, and a "" where it is not found.