can I ask now many mondays or tuesdays occur in my data?

  • Thread starter Thread starter tdiv
  • Start date Start date
T

tdiv

In my data set, I have what I refer to as nominal data like day of the week,
location, etc. I want to know if in xcel I can sort these items and
determine how many I have of each..like how many mondays, how often does a
location occur... can someone point me in teh right direction for information?
 
I'd use a simple approach.

I'd add another column to my data and return the day of the week.

=WEEKDAY(A2,2)&"."&TEXT(A2,"ddd")

This will give results like:
5.Fri
6.Sat
7.Sun
1.Mon
2.Tue
3.Wed
4.Thu
5.Fri
6.Sat
7.Sun
1.Mon
2.Tue
3.Wed

Then I could sort by this helper column -- and since I have that number at the
front, the Mondays will sort first.

After I have that column (and sorted), I could use data|subtotals.

Or I could drop the sort and use a pivottable.
 
ps, I could use:

=countif(B:B,"1.Mon")
to count the number of mondays in column B.
 
In my data set, I have what I refer to as nominal data like day of the week,
location, etc.  I want to know if in xcel I can sort these items and
determine how many I have of each..like how many mondays, how often does a
location occur... can someone point me in teh right direction for information?
Click to expand...

Pivot Table!

Assuming you have a list of data, create a pivot table based on the
data and count on a column you're interested in. Tons of examples out
there, no formulas and fast.
 
Back
Top