applying formula to a column

  • Thread starter Thread starter Ryan H.
  • Start date Start date
R

Ryan H.

Hello,

I have the three columns, A, B and C and their first row contains headers.
Here is an example:

HeaderA HeaderB HeaderC
2 5
7 1
15 4
9
3

I want column C to automatically calculate the sume of columns A and B. So
for the above example, I would have column C filled up with (7,8,19,9,3).
Also if both cells in columns A and B are blank, column C will be blank.

I applied the following formula to the whole column C by clicking on the C
column and after entering the formula I pressed CTRL-ENTER:
=IF(OR(ISNUMBER(A2),ISNUMBER(B2)),A2+B2,"")

This creates two problems:

1) It doesn't take into consideration the header column.

2) The formula is applied to each and every cell in the column. Can't this
be done any other way? I think its overload to apply it to every cell in the
column.


What can I do? Note that the summing i'm doing here is not what I'm really
going to do...
 
You can always do =A1+B1 then turn of dispalying zeros under
Tools/Options/View
Gavin.Bird
 
If I understand your message well, the old fashioned formula


=if(and(A2="",B2=""),"",A2+B2) and copied down


works fine, as I tried it.


****************************************
 
how about a formula like:

=if(count(a2:b2)=0,"",sum(a2:b2))

I'm assuming that you aren't going to put text in either A or B.

As code:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim LastRowB As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row
If LastRowB > LastRow Then
LastRow = LastRowB
End If

.Range("c2:c" & LastRow).Formula _
= "=if(count(a2:b2)=0,"""",sum(a2:b2))"
End With

End Sub
 
One idea for vba code might be something like this:

Range("C1:C10").FormulaR1C1 = _
"=IF(COUNT(RC[-2]:RC[-1])=2,SUM(RC[-2]:RC[-1]),"""")"

Which would put in say C2:
=IF(COUNT(A2:B2)=2,SUM(A2:B2),"")

HTH
Dana DeLouis
 
Back
Top