How to count the number of specific dates in a column containing formulas and blank text

[chindog]

I am having a problem with constructing a formula to count the number of instances a particular date occurs. I'm using the sumproduct function, and getting the #VALUE! error.

In the example below, I want to count the number of instances the date 9/17/21 occurs in column B. It should be 3. Column A is text copied from another source. Column B converts that text to date format. Column C has my failed formula. Column D has a copy and paste special (value) of the entry for 9/17/21 in column B.

There are rows in the range that are populated with the formula result "". I want to ignore those in my sumproduct formula.

Any help would be appreciated!

test sumproduct.xlsx
	A	B	C	D
1	Text date	Date Value	Count number
2	09/17/2021 arrive	9/17/2021		44456
3	09/19/2021 arrive	9/19/2021
4
5	09/20/2021 arrive	9/20/2021
6	09/17/2021 arrive	9/17/2021
7	09/19/2021 arrive	9/19/2021
8	09/17/2021 arrive	9/17/2021
9			#VALUE!
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(A2="","",DATEVALUE(LEFT(A2,10)))
C9	C9	=SUMPRODUCT(--(DATEVALUE(B2:B9)=44456),(B2:B9<>""))

 

[offthelip]

I think countif will solve your problem like this::

=COUNTIF(B2:B9,DATEVALUE("09/17/2021"))

 

[chindog]

I think countif will solve your problem like this::

=COUNTIF(B2:B9,DATEVALUE("09/17/2021"))

Thanks so much! That worked!