How to add the smallest of two columns in Excel

I have two columns, each containing either a number (zero is allowed) or a blank cell.  I want to get the total of all the numbers in column A except if there is a number in column B.
Example:
A     B
3
2     0
4     2
The total should be 3 + 0 + 2 = 5.
Stephan GrossSenior Manager, Information TechnologyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this ARRAY formula

=SUM(IF(B1:B3="",A1:A3,B1:B3))

To enter an array formula
Press Ctrl-Shift-Enter

After you enter the formula do you see braces around the formula in the formula bar?

If not then
After entering the formula
Select the formula cell
press F2
press Shift-Ctrl-Enter
0
 
Rob HensonFinance AnalystCommented:
Formula in column C:

=IF(B2="",A2,B2)

Then add up column C

Thanks
Rob H
0
 
Ken ButtersCommented:
One way would be to add a third column.

Add a formula to cell C1 as : =IF(ISBLANK(B1),A1,B1)
then do a fill down on column 'C'

Now you can sum column C to get the answer you want.

A second way would be to write a VBA macro to add the column for you.  I can help with that solution if you'd rather do it that way.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rob HensonFinance AnalystCommented:
Going by the title, maybe you need the smallest of the two columns:

=IF(B2="",A2,min(A2,B2))

Can scenario arise where A is blank but B has number?
0
 
Rob HensonFinance AnalystCommented:
Looking at it, blank is ignored in the MIN function so just the following in column C will get desired result:

=MIN(A2:B2)

Then sum of column C

Thanks
Rob H
0
 
Stephan GrossSenior Manager, Information TechnologyAuthor Commented:
Thanks to all but Syed's solution was the only one that didn't require me to add another column.
0
 
Rob HensonFinance AnalystCommented:
If its always the smallest of the pair, I don't believe Syed's will work when the smallest is in column A.
0
 
Saqib Husain, SyedEngineerCommented:
Hi, Rob. You are right about the title but the description in the question is different and that is what I based my solution on waiting for feedback from the asker which came in the form of acceptance.
0
All Courses

From novice to tech pro — start learning today.