0

I have data in column A. Some of the values are dates and some are not:

Cat
1234
12/25/2016
$123.45
6/10/2016

I want to count the number of cells in the column that contain dates of a specific format. I am interested in counting cells for which the CELL() worksheet function returns "D4".

I can do this with a helper column; in B1 I enter:

=CELL("format",A1)

and then copy down. Then in another cell:

=COUNTIF(B:B,"D4")

enter image description here

I can also do this with a VBA UDF:

Public Function DateCounter(rng As Range) As Long
    Dim r As Range
    For Each r In rng
        If IsDate(r) Then DateCounter = DateCounter + 1
    Next r
End Function

I would like to do this without VBA and without a "helper" column.

What I have tried is:

=SUMPRODUCT(--(CELL("format",A1:A5)="D4"))

But this always returns 0 (either as a normal formula or an array formula)

  • Are your other number ever between 41,000 and 43,000? If not just use COUNTIFS() looking for numbers between those. – Scott Craner Jun 10 '16 at 14:24
  • @ScottCraner Good idea.............Any clue why the SUMPRODUCT() fails ?? – Gary's Student Jun 10 '16 at 14:32
  • As far as I can tell it is the CELL() formula, it does not like to be used as an array so it will only return the value of the first cell, which in this case is "G". If you changed the first cell to a date you would get 5. – Scott Craner Jun 10 '16 at 14:45
1

CELL() does not support arrays. It is an old and well known limitation; I think there is not much you can do, except using your own logic instead of CELL() to check for a date format, where you check for slashes in the proper positions, valid month numbers, etc., etc.; that would be tedious but there is no other way.

  • .......................thanks................ – Gary's Student Jun 12 '16 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.