=sumproduct returns #Value

[Bandito1]

Hello,

My formula =SUMPRODUCT(--(TEXT('[Registratie review.xlsx]2016-2022'!$H$:$H$;"mmmm")=B3)*(YEAR('[Registratie review.xlsx]2016-2022'!$H$:$H$)=B$1)*('[Registratie review.xlsx]2016-2022'!$H$:$H$>0))

returns #VALUE

I read on internet that this is caused because a cell is formatted wrong or contains text.
The H column consists out of 40813 rows..

When i filter i see only dates.
I select the whole H column and format to dates. Still #VALUE

Since the files consists out of 40813 rows.
Is there a fast way to find out what cell is causing this?

 

[steve the fish]

Formatting wont change the value of the cell. It will still be textual regardless of the formatting. Its pretty likely you have dates that arent true dates if you are getting value error.

 

[Bandito1]

I see, and they show up like normal dates when i filter?
Is there a way to find these non true dates?

 

[Bandito1]

I think the problem is; sometimes it's 01-okt-22 and sometimes 01-10-22

Any way to find those xx-okt-22 so i can change them easily to xx-10-22?
40813 rows with dates..

 

[RoryA]

Do you have headers in row 1 by any chance?

 

[Bandito1]

Yes i do, data starts in row 2

 

[RoryA]

That's the problem then. You are applying the YEAR function to an entire column which includes the header row (and is very inefficient). If the header row is text, that will return a #VALUE error and stop the formula from working. Can you not limit the ranges used?

 

[Bandito1]

Changed it $H$2:$H$1000000

Tried to look up how to do H2 to end of data in column but can't find that

 

[RoryA]

Is 1m rows really necessary? If you only have about 48k rows of data I'd say that seems like overkill...