COUNTIFS and DATEVALUE

[Doowle]

I have hundreds of rows that contain a date stored in text format and a name, it's a holiday reconciliation report.

I want to count the number of entries there are for each month and then also the number of entries for each person.

I've been trying to use something similar to this, =COUNTIFS(F8:F11,">=" & F5,F8:F11,"<" & EDATE(F5,1)), to count the entries by month.

F5 is the start of the month in question, i.e. 01/08/2018

However this doesn't work because the dates are stored as text, I can't do this - =COUNTIFS(DATEVALUE(F8:F11),">=" & F5,F8:F11,"<" & EDATE(F5,1))

So what are my alternatives?

Thanks in advance

 

[shaowu459]

try:

=SUMPRODUCT((F8:F11-F5>=0)*(F8:F11-EDATE(F5,1)<0))

 

[Doowle]

Thank you and sorry for not replying earlier - appreciated.

 

[shaowu459]

：） the most important thing is you solved your problem