How do I bin numbers by a date in an Excel sheet

[Guest]

I have imported the site vistor data into a spreadsheet. I wish to sort the
dates by number of visits for each day. So I would probably end up counting
the visits for each day. What function in Excel allows me to do this?

 

[mangesh_yadav]

Assuming your data is something like
1-jun-05 25
2-jun-05 45
3-jun-05 18
and so on

Simply select both the columns, go to sort, and sort on column B

Manges

 

[Guest]

If the visitor sheet has multiple entries for each date, because each person
signs in with the day, try using a pivot table with count in your data area
and date as a row heading. Or, if one line in the visitor book can have more
than 1 visitor (e.g. a group is signed in), use sum in the data area. The
pivot table can also be sorted by decreasing number of visitors.

 

[Guest]

The vistor data is in a single column in Y/M/D format. I am kind of stuck
here. There are 800 such entries. A sorting function helps me only partially.
After this I wish to count number of entries for each date

 

[Guest]

the column just has entries in YMD format. I can change and sort the format.
Beyond this, I need to count how many entries are there for each date. Below
is a portion of the data

17-Jun-05
17-Jun-05
17-Jun-05
17-Jun-05
17-Jun-05
17-Jun-05
17-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
16-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
15-Jun-05
14-Jun-05
14-Jun-05

 

[mangesh_yadav]

Did you try the count functions. Also sumproduct could help, somthing
like

=SUMPRODUCT(--(A1:A200="17-Jun-05"),B1:B200)

please check with the date format, or you could use DATE(2005,6,5)
instead of "17-Jun-2005"


Mangesh

 

[Guest]

Sorry. I've been unable to get onto this site for a few days. If you put a
Heading in the cell above your data column, you can still use a pivot pretty
quickly.
Just drag Heading into the Row Area and Heading again into the Data Area,
using the Count function as your aggregation.

This will give you something that looks like:
Count of Dates
Dates Total
14-Jun-05 2
15-Jun-05 12
16-Jun-05 25
17-Jun-05 7
Grand Ttl 46

Cheers,
Trent

 

[Guest]

Sorry. I've been unable to get onto this site for a few days. If you put a
Heading in the cell above your data column, you can still use a pivot pretty
quickly.
Just drag Heading into the Row Area and Heading again into the Data Area,
using the Count function as your aggregation.

This will give you something that looks like:
Count of Dates
Dates Total
14-Jun-05 2
15-Jun-05 12
16-Jun-05 25
17-Jun-05 7
Grand Ttl 46