2

I have a list of dates in a column in Excel and would like to show a total for each unique date.

So say this is the original list:

03/05/2019
03/05/2019
03/05/2019
03/05/2019
03/05/2019
07/05/2019
07/05/2019
07/05/2019
08/05/2019
08/05/2019
08/05/2019
09/05/2019
09/05/2019

I'd like to convert that to this:

03/05/2019,5
07/05/2019,3
08/05/2019,3
09/05/2019,2

Thanks for your help!

1

You first need to get a column consisting of unique values. This can be done following this:

https://support.office.com/en-us/article/filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

or if you want to manually use equations,

https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/

Then, to get the actual counts of those unique values, you can use =COUNTIF(). So say your original list is in A1:A13 and the unique values are in B1:B4. You would do =COUNTIF($A$1:$A$13,B1) in B1, and copy/paste that to B2, B3 and B4.

0

If your goal is to actually have each unique value with it's count in one cell seperated with a comma you could try this rather long formula:

enter image description here

Formula in C1:

=TEXT(INDEX($A$1:$A$13,SMALL(INDEX((MATCH($A$1:$A$13&"",$A$1:$A$13&"",0)<>ROW($A$1:$A$13))*10^10+ROW($A$1:$A$13),0),ROW(1:1))),"dd/mm/yyyy")&","&COUNTIF($A$1:$A$13,INDEX($A$1:$A$13,SMALL(INDEX((MATCH($A$1:$A$13&"",$A$1:$A$13&"",0)<>ROW($A$1:$A$13))*10^10+ROW($A$1:$A$13),0),ROW(1:1))))

Drag down...


Hindsight, your requirement was different but i'll leave the other option there for future reference. With headers and a column for unique values and another one for the appropriate count see example below:

enter image description here

Formula in C2:

=INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$14),0),0))

Formula in D2:

=COUNTIF($A$2:$A$14,C2)

Drag both down...

  • CSE formula or standard? – Forward Ed Aug 9 at 15:10
  • @ForwardEd just standard would do – JvdV Aug 9 at 15:12
  • Thanks JvdV, I think this is close to what I'm after. However, what I should have said in my example is that the comma is another column. So the values after the comma in your screenshot would go into column D. Would that also reduce the complexity of the formula? Your is an impressive beast! – Wes Aug 9 at 16:07
  • @Wes it would drastically change the formula. Do you also have headers? Or would your data start at row one – JvdV Aug 9 at 16:19
  • Sorry that I wasn't clear before. Yes, there would be headers. – Wes Aug 9 at 16:31
0

You can do this with formulas, and would probably want that if your data is dynamic and you want the result to be automatically updated. Otherwise, I agree with Scott Craner's comment about using a pivot table. You can get your result with a few clicks of the mouse.

enter image description here

You'll want a column heading. You just select the data and then insert a pivot table from the menu or toolbar. Drag the column heading to the Rows window and to the Values window (and verify that the aggregation method selected is Count). There are a couple of administrative selections, like where to stick the pivot table, and whether to include a grand total.

And you're done.

  • I'd forgotten about pivots, my Excel is extremely rusty! Thank you, this also works well. – Wes Aug 10 at 7:34

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.