i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".

Hi lisa_p -

For an instantaneous count, you could use the following formula in cell B74:

=COUNTIF(B4:B54,"Y")

If you want to add counts the count already in cell B74, you could try the
following VB procedu

Sub lisa_p()
Range("B74") = Range("B74") + _
WorksheetFunction.CountIf(Range("B4:B54"), "Y")
End Sub

--
Jay

"lisa_p" wrote:

i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".

One way:

=COUNTIF(B4:B54,"Y")

In article ,
lisa_p wrote:

i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".

You can't have a function reference itself. This is called circular
reference. What you can do is a worksheet change function that can detect
when either B4 or B54 is changed. I didn't know what "Y" was so I changed it
to 34

Sub worksheet_change(ByVal Target As Range)

If Target.Row = 4 And Target.Column = 2 Then
If ActiveSheet.Cells(4, 2) - ActiveSheet.Cells(54, 2) = 34 Then
ActiveSheet.Cells(74, 2) = ActiveSheet.Cells(74, 2) + 1
End If
End If
If Target.Row = 54 And Target.Column = 2 Then
If ActiveSheet.Cells(4, 2) - ActiveSheet.Cells(54, 2) = 34 Then
ActiveSheet.Cells(74, 2) = ActiveSheet.Cells(74, 2) + 1
End If
End If

End Sub




"lisa_p" wrote:

i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".