CountIF for dates

[Buzz1126]

I need to count the number of treatments on a certain date (which is the number of times a certain date appears). Attached is a representative sample of my data. I think the problem lies in the fact that the data is in the format "1/3/2017 8:00:48 AM" but only shows in the cell "3-Jan". I'm currently using this formula:

=COUNTIF($F$3:$F$62, B4)

So, in the data I want to count how many times 3-Jan appears in Column F. and place this count in column C next to 3-Jan in column B (place count in cell C6). I've got a full year of data so ideally you use reference cells so that I don't have to manually type each date in CountIf Formula.

Thanks in advance guys,

Matt

[63falcondude]

In C4, try

=SUMPRODUCT(--(INT($F$3:$F$62)=B4))

Drag this down column C.

[Buzz1126]

So that works in my example but not in my real data for some reason. I'm getting the #Value error so i'm looking into it. Thank you

[Buzz1126]

Fixed by copying to new tab and filtering out words. Works that way. Thank you

[63falcondude]

Just FYI: All dates and times in Excel have a numerical value. The date is the integer part of the number whereas the time is the decimal part.

For example, January 3, 2017 3:00 AM has a numerical equivalent of 42738.125 (1/3/2017 = 42738 and 03:00 = 0.125)

The INT function pulls out the integer value of a cell (i.e. the date without the time).

The SUMPRODUCT formula counts the number of cells in the range (F3:F62) where the date (the integer value) is equal to the date in B4.

[63falcondude]

Thanks for the rep! If that solved your question, please mark this thread as SOLVED.

[Buzz1126]

Its cuz i'm a die hard Falcons fan

[63falcondude]

1963 Ford Falcon.

I guess I stole that rep point!