Counting Dates across rows

[erniepoe]

Hi all, I am trying to find the number of dates in G1:C1 that equal the year of the cell in A1, but I am getting a value error. When I use this formula down columns rather than across rows, it seems to work fine. Is there a workaround to get this to work across rows?

=SUMPRODUCT(--(YEAR(G1:CM1)=YEAR(A1)))

 

[Fluff]

That formula works fine. Check that your dates are real dates & not text.

 

[Dave Patton]

N.B. Which range do you want to calculate, the range in the description or the range in the formula?
Ensure that you do not have text or error messages in the range.
Sum will also work.

T202310a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	8-Oct-23		5-May-23	10-Oct-23			2-Feb-23			22-May-23	10-Oct-24
2
3
4		2	2
5		3	3
2d

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(--(YEAR(G1:CM1)=YEAR(A1)))
C4	C4	=SUM(--(YEAR(G1:CM1)=YEAR(A1)))
B5	B5	=SUMPRODUCT(--(YEAR(C1:G1)=YEAR(A1)))
C5	C5	=SUM(--(YEAR(C1:G1)=(YEAR(A1))))