Excel formula to count occurrences of a value in a different sheet

[BugZap13]

I am trying to count the number of rows in a different sheet containing a value.
In sheet 2 I have sales dates in column "A". In sheet 1 I want to count the number of sales for 2024.
So I wanted to use YEAR() around the range which does not seem to work. I then tried using the range containg the sales date with the AND() wrapped around the two conditions without success. Seems like a reasonable request but can't seem to come up with the correct formula.

=COUNTIF(YEAR('Sheet 2'!A$6:A$999),2024)
=COUNTIFS(YEAR('Sheet 2'!A$6:A$999),'=2024')
=COUNTIFS('Sheet 2'!A$6:A$999,AND(">=01/01/2024","<=12/31/2024"))

The solution is probably simple, but I have not stumbled on a solution yet.

BTW the year 2024 is stored in "Sheet 1"!J2 but have it hard coded above to try to get a solution.

TIA, Mark

 

[macropod]

For example:

=SUMPRODUCT((Sheet2!A$6:A$999>DATE(J2,1,0))*(Sheet2!A$6:A$999<DATE(J2+1,1,1)),(Sheet2!A$6:A$999<>0)*1)

 

[macropod]

Another way:

=COUNTIF(Sheet2!A$6:A$999,">"&DATE(J2,1,0))-COUNTIF(Sheet2!A$6:A$999,">"&DATE(J2+1,1,0))

 

[combo]

A little shorter formula with SUMPRODUCT:

=SUMPRODUCT((YEAR('Sheet 2'!A6:A999)=2024)*1)

 

[BugZap13]

Combo your solution worked like a champ.

I did not try the other ones but conceptually they should work also.

Thanks everyone... Happy New Year!