excel b74=b74+1 when b4-b54 ="Y"
i want to add 1 to b74 each time any cell b4-b54 is equal to "Y".
|
excel b74=b74+1 when b4-b54 ="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". |
excel b74=b74+1 when b4-b54 ="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". |
excel b74=b74+1 when b4-b54 ="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". |
All times are GMT +1. The time now is 10:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com