Count Help - a column of different dates I need to compact into how many of each date exists

[Iotasigma]

Hello everyone, first time poser. I read the FAQ and i searched and searched but I could never get the words right to get the answer.

I have a column of cells with a range of dates and times:

for example:[TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]5/11/2018 8:22[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5/15/2018 8:24[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5/18/2018 7:48[/TD]
[/TR]
[TR]
[TD="align: right"]5/18/2018 9:23[/TD]
[/TR]
</tbody>[/TABLE]

I need the function that looks at the entire row and tells me how many cells contain 5/11 and 5/15 etc... I need it to disregard the time in the cell. and just give me the number of times the specific data appears. For example in the numbers above 5/11/18 would be 1, 5/15/18 would be 1 and 5/18/18 would be 2.

Thank you for any help.

-Jon

 

[Aladin Akyurek]

Try something like:

=SUMPRODUCT((INT($A$2:$A$5)="2018-05-18"+0)+0)

 

[Rick Rothstein]

You layout is not clear from your post, but perhaps you can adapt the following. Assuming your posted values are in cells A1 through A4, put this formula in cell B1 and copy it down...

=SUMPRODUCT(--(INT(A$1:A$4)=INT(A1)))

 

[Iotasigma]

You layout is not clear from your post, but perhaps you can adapt the following. Assuming your posted values are in cells A1 through A4, put this formula in cell B1 and copy it down...

=SUMPRODUCT(--(INT(A$1:A$4)=INT(A1)))

Thank you both so much this worked!

 

[xerxers]

Rick,
Would you please be able to take the above for me one step further.
I entered the formula and then copied it down but the total number occurrences for each date appeared the same number of time so if a date had 5 occurrences then the number 5 appeared in the 5 date locations.
Could you please tell me how I can enter the count just against the first occurrence of each unique date.
Thank you I appreciate any assistance.

Robert

 

[Aladin Akyurek]

@ xerxers


If you are trying to count unique occurrences of dates say in A1:A10...

=SUM(IF(FREQUENCY(A1:A10,A1:A10),1))


Otherwise, try to elaborate your question by posting a small sample along with the desired result.

 

[xerxers]

DATE CLOSED
Column C Column D

11/01/18
11/01/18
11/01/18
11/01/18
11/01/18 5
11/02/18
11/02/18
11/02/18
11/02/18
11/02/18
11/02/18
11/02/18 7
11/05/18
11/05/18
11/05/18 3

So the above is found in Column C. I would like to add a column "D" and in that column total how many times a date appears in column C, but I only want that total to appear once in column D on the last occurrence of the new date.
Does that help and or make sense.

By the way thank you very much for responding I appreciate it.

Robert

 

[Aladin Akyurek]

In D2 enter and copy down...

Either:

=IF(COUNTIFS($A$2:$C2,$C2)=COUNTIFS($C$2:$C$16,$C2),COUNTIFS($C$2:$C$16,$C2),"")

Or:

=IF(C2<>C3,COUNTIFS($C$2:$C$16,$C2),"")

The latter requires that entries are grouped.

 

[xerxers]

Gentlemen,

Thank you worked like a charm. Made my day....

 

[Aladin Akyurek]

Gentlemen,

Thank you worked like a charm. Made my day....

Thanks for the feedback.