Dynamic (SUM OFFSET) formula to sum across a row

To byundt,
  Thankx for the input. Yes, I've done that, entered it once, then copied it down...Problem is, (a) I can't hook the formula to a named range that way, to be possibly used in other areas of the worksheet, and (b) for all the affected cells in Col D that don't yet have data from cols A,B & C, you end up with a bunch of zeroes or other trashy formatting in col D. I would MUCH PREFER to have the SUM OFFSET formula with the DYNAMIC twist to it.
Have fun!

riprap1951,
The following SUMPRODUCT formula will be the same anywhere in column D:
=SUMPRODUCT((ROW(OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3))=ROW())*OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3))

It returns a 0 if it extends beyond the data, however. To overcome that deficiency will require an IF function.
=IF(SUMPRODUCT((ROW(OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3))=ROW())*OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3))=0,"",SUMPRODUCT((ROW(OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3))=ROW())*OFFSET(A$2,0,0,COUNTA($A$2:$A$65536),3)))

I could shorten the second formula by making it an array formula.
Brad

riprap1951,
If you just want a simple defined formula, then go back to the initial suggestion. Select cell D2, then in the Names in Workbook field, enter a name like "mySum". Then in the Refers to field, enter this formula:
=IF(COUNTA(Sheet1!A2:C2)=0,"",SUM(Sheet1!A2:C2))

The formula in column D would then become:
=mySum

Brad

riprap1951,
A different approach would be to use a VBA sub to insert the formula in column D when data is added to columns A, B or C. If all the data is deleted from a row, then the formula is removed. Such a sub would go in the code pane for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Range, targ As Range
Set targ = Intersect(Target, Range("A2:C65536"))
If targ Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rw In targ.Rows
    If Application.CountA(Cells(targ.Row, 1), Cells(targ.Row, 3)) = 0 Then
        Cells(targ.Row, 4) = ""
    Else
        Cells(targ.Row, 4).FormulaR1C1 = "=SUM(RC1:RC3)"
    End If
Next rw
Application.EnableEvents = True
End Sub

To paste the sub in the code pane for a worksheet, right-click the worksheet's sheet tab. Then choose View Code from the pop-up. Paste the code there, then ALT + F11 to return to the spreadsheet.

Brad

To byundt,
   Sorry, I can't get any of those suggestions to work. But I'm gonna keep trying!
   Have fun ((-;
riprap

riprap,
The two methods I like best out of the ones I posted are the last two.

If you are still stuck, please describe the problem you are facing. Or consider sending me a copy of your workbook (double-click on my name to find the e-mail address in my EE profile).
Brad

Brad,
  thanks a ton for hanging in there on this one...you inspired me to go out and buy a book on VB.
  have an excellent day [:-)
terry.