column of items - determining frequency

  • Thread starter Thread starter vnl
  • Start date Start date
V

vnl

I have a column of items as follows:

car
car
car
car
truck
boat
boat

How can I create a formula that will determine the frequency that each item
occurs in the list. I seem to recall that the frequency function does
something like this. I want the end result to be something like this:

car 4 times
truck 1 times
boat 2 times

Any idea how to accomplish this?

Thanks.
 
=countif(a1:a100,"Car")
or if b1 held car:
=countif(a1:a100,b1)

If your list of items can vary a lot, you may want to look at Data|pivottable.

It can create these summaries really quickly--once you're comfortable with them.
 
Also use the "concatenate" formula to add the word times to the "countif" result i.e.
=concatenate(Countif(a1:a100,"Car") ," times")
 
Dave Peterson said:
=countif(a1:a100,"Car")
or if b1 held car:
=countif(a1:a100,b1)

If your list of items can vary a lot, you may want to look at
Data|pivottable.

It can create these summaries really quickly--once you're comfortable
with them.
Click to expand...

Thanks. I just realized that what I need to do is to not only record the
individual occurances (i.e. counting the number) but also check the
frequency of a range of numbers in another column. For example, say that
I have the following column:

1
1
1
5
5
5
12
12
13
13
14

I need the end result to have something like below:

Frequency of #1-4: 3 times
Frequency of #5-9: 3 times
Frequency of #10-15: 5 times

Any ideas?

Thanks.
 
Take a look at =Frequency() in excel's help.

You can have "bins" that sound like what you want.
 
Back
Top