Problem with a Conditional Sum

lbarrett

New Member
Joined
Aug 27, 2002
Messages
13
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Aug 27, 2002
Messages
13
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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.
 

Forum statistics

Threads
1,144,358
Messages
5,723,891
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top