Finding Number of Times a Specific Year Value is Used

[kajohnson99]

I'm a novice at Excel VBA. I have a workbook with multiple sheets. In column C on each sheet, there may be a date value. I want to search each sheet and count the number of times the year value of the date is the same as the input year. The input year is in a specific cell on sheet TOC. I am calling the function from a cell in TOC.
I seem to go into an infinite loop with the system continually beeping at me. My function code is attached. some one tell what I'm doing wrong?

Public Function SumValuesByYear()

Dim ws As Worksheet
Dim SumYears As Long
Dim cell As Range
Dim TargetYear As Range
Dim nbCells As Integer
Dim sYear As Long


Set ws = ActiveSheet
sYear = Year(ActiveCell.Value)
'sYear = "2022"
SumYears = 0
' Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets
SumYears = 0
If ws.Name <> "TOC" Then
nbCells = 0
'sum values by year using the For Loop
For Each cell In ws.Columns(3).Cells
' For nbCells = 1 To 35
If IsDate(cell.Value) Then
If Year(cell.Value) = sYear Then
SumYears = SumYears + 1
End If
End If
' Next nbCells
Next cell
End If
Next ws
SumValuesByYear = SumYears
End Function

 

[kajohnson99]

The answer to your question is yes. I can select all the sheet that I wanrt to sum across. I will try your formula.
Thanks,
Keith

 

[kajohnson99]

Here is a screen shot showing how I entere3d the code and the error message I get (#Name?)

 

[kvsrinivasamurthy]

What is the function entered in G5?

 

[Fuji]

1) Insert a New Module and never name it as same as the function name.
2) copy the code and paste to 1)

Function CountByYear&(myYear&)
    Dim s$, ws As Worksheet
    For Each ws In Worksheets
        If UCase$(ws.Name) <> "TOC" Then
            s = ws.Range("c1", ws.Range("c" & Rows.Count).End(xlUp)).Address
            s = Replace("sum(if(isnumber(#),if(year(#)=" & myYear & ",1,0),0))", "#", s)
            CountByYear = CountByYear + ws.Evaluate(s)
        End If
    Next
End Function

3) Use in cell like
G5:
=CountByYear(F5)