Excel Formula for categorizing several values

Question

I have a very long list of values.

I would like to categorize them into different groups (even the duplicates)

examples of the groups would be:

" Group A (or 1) = 0 < x < 5 "

" Group B (or 2) = 6 < x < 14 "

....

" Group G ( or 7) = x > 70 "

How would i create the formula assuming the values i want to use are all within the same column?

The use of this formula is to find the total quantity of items that fall within a particular price range (the 0 and 5 in the example above would be dollar values) - this is to update total inventory quantities.

any help is appreciated.

-thx

__________FURTHERMORE___________

I saw this response in another thread which seemed to be what i wanted however i am unsure how to properly type in the formula

Begin Quote:

"I'll assume that you've already determined the number of categories you'll use. Let's say you want to use 20 categories. Then they will be:

Category 1: [0 - 100,000) Category 2: [100,000 - 200,000) Category 3: [200,000 - 300,000) ... Category 19: [1,800,000 - 1,900,000) Category 20: [1,900,000 - 2,000,000] Note that the label of each category can be defined as

FLOOR (x / category_size) + 1

This is trivial to define as a computed column in SQL or as a formula in Excel.

Note that the last category is infinitesimally larger than the others, since it is closed on both sides. If you happen to get a value of exactly 2,000,000 you might erroneously classify it as falling into category 21, so you have to treat this exception with an ugly "IF" (in Excel) or "CASE" (in SQL)." /end

I assume that "FLOOR (x / category_size) + 1" is the formula i want to use. however i do not understand how to plug in my cells.

Answer 1

Write an array using your groups:
0 Group A
6 Group B
15 Group C
22 Group D
35 Group E
70 Group G
Saying the array is A1:B6 and your values are in column C, in D write the following:
=VLOOKUP(C1,$A$1:$B$6,2)
and you can drag it down for all values you will get Group A, Group B as response

Answer 2

If your goal is to count the number of items matching a single criterion you could use a COUNTIF formula.

How do you use it?

Syntax:

 =COUNTIF(range, criteria)

For example:

=COUNTIF(A2:A5,"apples")

or

=COUNTIF(A2:A5,A4)

DATA        | DATA

12"Sub      |  5

Luggage     | 60

Pens        |  6

Quarterback | 25

Pencils     | 60

Toasters    | 57

Of course, since you are looking to count total number of items between two numbers your totals are derived from two COUNTIF formulas.

=COUNTIF($B$2:$B$7,">="&B10)-COUNTIF($B$2:$B$7,">"&C10)

The first part counts the number of items meeting the lower boundary of your criteria.

=COUNTIF($B$2:$B$7,">="&B10)

And the second excludes those items that are too large for your upper boundary.

-COUNTIF($B$2:$B$7,">"&C10)

See the picture for a more in depth look: