How to count each of the unique entries in a column?

[FISH]

I have a large column with many entries. Many of these are duplicates so I'd
like to be able to count how many ABC entries are in the column, and how
many DEF entries, etc...

For example I have a column in C which lists the items and another column F
which lists the dates that item was acted on.

I'd like to see how many items were acted on each of the different dates.

So is there some way to get results something like this:
11/27/2008 = 43
11/28/2008 = 65
11/29/2008 = 32

Any format of the results would actually be fine as long as it tells me how
many items were acted on each of the dates listed in column F.

Thanks in advance.

 

[macropod]

Hi Fish,

If you set up a column with the dates in it (eg G1:G10) and put a formula like:
=COUNTIF(F:F,G1)
into H1 and copy down to H10, you get the tally for each date.

If you need to constrain the tally by the 'ABC' or 'DEF' designations in column C, for which the data spans the 1st 100 rows, you
could use a formula like:
=SUMPRODUCT((C$1:C$100="ABC")*(F$1:F$100=G1))
in H1 and copy down to H10, to get the tally for the 'ABC' category for each date.

 

[Shane Devenshire]

Hi,

I'm a little unclear what you are asking are you trying to count ABC, DEF
and so on, or dates, or both?

You already have an answer for 2003 although i might consider the following
variation of SUMPRODUCT:

=SUMPRODUCT(--(C$1:C$100=H1),--(F$1:F$100=G1))

Where H1 contains one of the items like ABC and G1 contains a date.

In 2007 a shorter formula would be

=COUNTIFS(C$1:C$100,H1,F$1:F$100,G1)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Don Guillett]

=COUNTIF($A$1:$A$21,A1)

 

[FISH]

Sorry I wasn't clear enough.

I have a column C with items in it and a column F with the dates things
happened with those items listed in column C.

I want to be able to count the total number of items that were acted on a
certain date.

So I'd like to put a formula in a cell that will count how many 11/28/2008
entries there are in column F and how many 11/29/2008 entries there are.

So for example If I have 39 items that were acted on 11/28/2008, I want a
quick way to add up the total times that 11/28/2008 is listed in column F
and add that result in a specific cell (say G1). Then add in G2 a formula
that will give me the total times the 11/29/2008 entry is listed, and so on.

I hope that clears it up a little. If not, let me know and I'll try again

 

[Don Guillett]

If you have dates in col F and you want to know how many of each date then
put your starting date in g1 and copy this down.

=COUNTIF(F:F,$G$1+ROW())

 

[FISH]

So add "11/26/2008" to G1 and then paste the formula you listed where?
If I add that formula after the 11/26/2008 to make G1 contain:
"11/26/2008 =COUNTIF(F:F,$G$1+ROW())" it doesn't work, so I don't think
that's what you suggested.

If I want the date in G1 and the total times that date has appeared in G2,
how would I do it/where would I put the date and the formula you mentioned?


Thanks for the help, and sorry for the newbness of my questions...lol.

 

[Don Guillett]

If you put the date to start in g1 and this formula in g2 and copy
down......

=COUNTIF(F:F,$G$1+ROW()-1)

 

[FISH]

Got that to work, thanks.

Now, is there a way to copy that formula for the other dates as well.

After getting things in place the column with the dates is still F, but now
I would like to put the times that dates starting in I1 and the times that
date has appeared starting in I2.

So I'd like I1 to have my first date and I2 to have the times that first
date has appeared. J1 and J2 would have the next date and result and so on
across the rest of the workbook.

Thanks to your help I have placed the first date (11/25/2008) in I1 and put
the formula you suggested (just changing it to I instead of G) in I2 and it
works great.

I then entered in my next date (11/26/2008) in J1 and tried to copy the
formula from I2 into J2, but it doesn't work.

Is there any way for me not to have to manually type in the formula for each
and every date I want counted (there will be many)?

Thanks for all the help!!!!

 

[Don Guillett]

Might be best to just send your workbook to my address below with a very
clear explanation and before/after examples.

 

[Don Guillett]

=COUNTIF($F:$F,I$2)