group by or count

  • Thread starter Thread starter demolay
  • Start date Start date
D

demolay

i have 3 columns in which dates are written. for example, in the firs
one there is the day, in the 2nd there is the month and in the los
there is the year. there are many dates and some of the are the same
such as;
column1---column2------column3
6------------september--1975
7------------october------1966
11-----------january------1980
6------------september---1975
...
i want to count the number of total dates. for example, there are
6-sep-1975, and 1 for the others. in short, i want to do somethin
about grouping by the columns and counting them. any suggestions?
prefer not to merge cells or something else.
 
Try this,

In a separate new column (column D)
insert the formula

=DATE(a1,MATCH(TRIM(UPPER(b1)),{"JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"},0),c1)

Fill down for all your data.

This assumes that you data starts on row 1, columns A to C, and will
return a date for the data in columns A-C

The =COUNTIF() function can then be used in column E to count the
number of occurences in the list of dates.
Use = COUNTIF(D1, $d$1:$d$10000) and fill down for all the data. If
your list is more than 10000 long, please amend the $d$10000 part of
the formula accordingly.
 
Kieran said:
Try this,

In a separate new column (column D)
insert the formula

=DATE(a1,MATCH(TRIM(UPPER(b1)),{"JANUARY","FEBRUARY","MARCH","APRIL","MAY",
"JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"},0),c1)
Click to expand...

If the OP could live with a #VALUE! error rather than #N/A if B1 weren't a
month name, then

=DATEVALUE(C1&" "&B1&" "&A1)

would be more efficient.
Fill down for all your data. ....
The =COUNTIF() function can then be used in column E to count the
number of occurences in the list of dates.
Use = COUNTIF(D1, $d$1:$d$10000) and fill down for all the data. If
your list is more than 10000 long, please amend the $d$10000 part of
the formula accordingly.
Click to expand...

I think you meant =COUNTIF($D$1:$D$10000,D1).
 
it is a little bit complicated.
what if i want to seperate the month part. i mean i want to count
column A and column C. for example, count the number of occurences of 6
- 1975.
how can i do this? what formula i should use in a different column, say
D column, to list all unique values; and then count them in another
column, say Column E.
 
The countif function will do this.

In an empty adjacent column enter =COUNTIF($D$1:$D$10000,D1).
(this assumes the new column is column D)
and then fill down.

The resultant number will the number of times the value in column D
occurs.

PS Thanks to Harlan Grove for correcting my last post in regards to the
countif parameter sequence.
 
Back
Top