VBA: Function that sums an offset cell for every cell in a range

[ackackack]

Hello!

I am trying to develop a Function that will Sum the values in column F, but only if the cell's row has "Clinical" in column D. My spreadsheet looks like this:

While I was able to use the IF Function to perform this [ =SUM(VALUE(IF(D13="Clinical",F13,0)),VALUE(IF(D14="Clinical",F14,0)), ... ... and so on], but I ran into a character limit when I extended this formula down to row 300. Since this Formula should be able to apply indefinitely, I was hoping there was a cleaner macro solution to this.

This is what I developed so far, and I'm not quite sure how to make it work because I'm fairly new to VBA:

Function ClinicalSum()
Dim cl As Range
For Each cl In Range("D13:D300")
    If cl = “Clinical” Then
        cl.Value = ActiveCell(2,0)
    End If
Next cl

End Function

If anyone has any suggestions, that would be AWESOME. Let me know if anything needs to be clarified.

Thanks!!!!!!!

 

[Jonmo1]

Try the SUMIF function

=SUMIF(D13:D300,"Clinical",F13:F300)

 

[ackackack]

Oh!! Awesome, I didn't realize that's how SUMIF worked. That is exactly what I need.

THANKS!!

 

[Jonmo1]

You're welcome.