Sum of multiple cells directed to one of two cells based on cell content

[Mari_Fec]

I have a spreadsheet that is used to track whether expenses can be billed back to our clients. When I enter "Billable" in column T, I would like the same of four cells in the same row (G, I, L, O) to be placed in a column named "Billable" in the same row; if I enter "Non-billable", I would like that sum to be placed in a column named "Non-billable" in the same row.

This is what I've come up with so far; if it worked I wouldn't be here

If (Range(ActiveCell.Value) = "Billable") Then
Range("R" & (ActiveCell.Row)) = WorksheetFunction.Sum(Range("G" & (ActiveCell.Row)) + Range("I" & (ActiveCell.Row)) + Range("L" & (ActiveCell.Row)) + Range("I" & (ActiveCell.Row)))
End If

Thanks in advance for your help!

 

[jorismoerings]

Hi,

This won't work the IF part will only be checked on confirmation of the cell but as soon as you've confirmed the input the active cell will change. So you can get this VBA part working but it will summarize the amounts in the row below the row in which you've entered Billable.

try this instead:

If Target.Value = "Billable" And Target.Column = 20 Then
Range("R" & Target.Row) = WorksheetFunction.Sum(Range("G" & Target.Row) + Range("I" & Target.Row) + Range("L" & Target.Row) + Range("I" & Target.Row))
End If

 

[Mari_Fec]

Hi,

This won't work the IF part will only be checked on confirmation of the cell but as soon as you've confirmed the input the active cell will change. So you can get this VBA part working but it will summarize the amounts in the row below the row in which you've entered Billable.

try this instead:

If Target.Value = "Billable" And Target.Column = 20 Then
Range("R" & Target.Row) = WorksheetFunction.Sum(Range("G" & Target.Row) + Range("I" & Target.Row) + Range("L" & Target.Row) + Range("I" & Target.Row))
End If

Thank you, but I get a message "Object Required" when I run this.

 

[jorismoerings]

Hi,

Most probably caused by the VBA but your question in OP was just to solve your question. I can't even start to imagine what the rest of your VBA looks like or what it should accomplish without seeing it.
My part as posted above works if you define it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Billable" And Target.Column = 20 Then
Range("S" & Target.Row).ClearContents
Range("R" & Target.Row) = WorksheetFunction.Sum(Range("G" & Target.Row) + Range("I" & Target.Row) + Range("L" & Target.Row) + Range("I" & Target.Row))
ElseIf Target.Value = "Unbillable" And Target.Column = 20 Then
Range("R" & Target.Row).ClearContents
Range("S" & Target.Row) = WorksheetFunction.Sum(Range("G" & Target.Row) + Range("I" & Target.Row) + Range("L" & Target.Row) + Range("I" & Target.Row))
End If
End Sub

Just some notes:
1. The code counts the amounts in column I twice, intentionally?
2.

I would like that sum to be placed in a column named "Non-billable" in the same row

Your OP didn't say in which column you want the sum placed if the code was unbillable. I just choose column S to be changed in the code by yourself to your need.

 

[Mari_Fec]

Thank you for continuing to look at this. I've actually decided to go another direction but I'm going to play around with this code because it looks interesting!