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?
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?