Count unique dates

[M.Siler]

I have a log and I'd like to count all the entries for each date. I'd
looked at DCOUNT but don't understand it. Here's an example of what I've
got

1/9/2004 fdadsfadsfadsfdsaf
1/9/2004 afagareterghnbsnhb
1/9/2004 hiodfjklfdsajkdfsajlk
1/9/2004 lkjadflkjadflkjdfjlkfd
1/10/2004 fdadsfadsfadsfdsaf
1/10/2004 afagareterghnbsnhb
1/10/2004 hiodfjklfdsajkdfsajlk
1/11/2004 lkjadflkjadflkjdfjlkfd
1/11/2004 fdadsfadsfadsfdsaf
1/12/2004 afagareterghnbsnhb
1/12/2004 hiodfjklfdsajkdfsajlk
1/12/2004 lkjadflkjadflkjdfjlkfd
1/12/2004 fdadsfadsfadsfdsaf

Is there a way I can get something from above that looks like:

1/9/2004 4
1/10/2004 3
1/11/2004 2
1/12/2004 4

 

[Niek Otten]

Use the Data>Subtotals command

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ken Wright]

Chuck the data in a pivot table. Will take you about 60 seconds once you know
what you are doing:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

You need to put a couple of headers on your columns though to use a Pivot table

 

[Dave Peterson]

If that list of dates can go on for a long time, you may want to add a row of
headers and

do:

Select your range
Data|Pivottable
Follow the wizard until you get to a step with Layout on it.
click on that Layout button
drag the Date Header to the row field
drag the "whateverthatis" header to the data field
Make sure it says "count of whateverthatis"
(double click on it and select count if it says sum of"
finish up the wizard

 

[JE McGimpsey]

Try a pivot table. Assume your list has a header ("Date"). Drag the date
header in to the PTs Row field, and Count of Date in the Data field.

 

[M.Siler]

Ok, I've been playing around with Pivot Tables and I must say they are VERY
cool. Here's where I am. I have the following data and I want to know the %
of Success and % of Failure on each day. I can get a count of how many items
are in each day, but how do I get the percentages?

Date Status
1/9/2004 Success
1/9/2004 Failure
1/9/2004 Success
1/10/2004 Success
1/10/2004 Success
1/11/2004 Failure
1/11/2004 Success
1/11/2004 Success
1/11/2004 Failure

 

[Peo Sjoblom]

To get that using your example assuming the data is in A2:B10

=SUMPRODUCT(--($A$2:$A$10=DATE(2004,1,9)),--($B$2:$B$10="Success"))/COUNTIF(
$A$2:$A$10,DATE(2004,1,9))

format as percentage

 

[M.Siler]

Very Interesting solution. I'm learing a lot from this question.

I was playing around with the Pivot Table Field setting and in the Options
they have "% Of" I selected "Base Field" of Status and then selected
Failure in hopes that it would give the me the % of Failure, but all I get
is "#N/A". I don't understand how this "% Of" options works?? Anyone work
with it before and can tell me what I'm doing wrong?

 

[M.Siler]

I got it.

Here's what I did. I dropped Date as the Row, dropped Status as the Column
then dropped Status as the Data. Now select the a field in the data area
then bring up the Field Settings window. Click the "Options >>" button and
change the "Show data as:" from "Normal" to "% of row" and click OK. POW!
Done!!

Thanks everyone for pointing me to Pivot Tables... they are very powerful!!

 

[Ken Wright]

LOL - Told you you'd like 'em <Chalk up another convert>