I just want to add a column to my SS that I can type in a choice of five words too.

Intensive
Weekly
fortnightly
Monthly
Dip

When I do this I want excel to give each word a numerical value Intensive 4, Weekly 3, Fortnightly 2, Monthly 1, Dip 0. The number is not to be displayed. Then at the bottom of the column I want to be able to total the numerical value.

Help please!

Bianchi Boy

similar question came up the other day and I worked out nice array forumla for it:

=SUM(LOOKUP(A1:A100, {“Intensive”,”Weekly”,”Fortnightly”,”Monthly”, “Dip”}, {4,3,2,1,0}))

to enter it as an array formula in, say A101, once typed or pasted into the forumla box press ctrl and shift and enter at the same time. It will give the forumla curly brackets making it an ARRAY FORMULA.

Can I suggest you use Tools> Validation> List to limit the entries cells in column A can be to just those that you have defined (In the List SOURCE box type Intensive, Weekly, Fortnightly, monthly, Dip.) – otherwise rogue entries might upset the array forumla etc. If you want to add new classifications you should be able to work out the obvious syntax order in the formula above.

Hi Stoner,

I really appreciate you taking the time to help me. This place is great some of the time!

Could I ask you to send me worksheet with the fomula working in it? i still don’t understand what I am doing wrong!

If you have time it’s ianwarnes at gmail dot com

Hi BB – file on it’s way.
My bad, I forgot to point out that the list in the forumla needs to be alphabetical.
Corrected in the file.

I would do it in a simpler way, using an additional hidden column.

Create a drop down menu in the column with the 5 things you want (using I think tools, create list, etc.

Then add a column next to it (which you can then hide) and use the nested if function which will be something like:

=if(cellx=”intensive”,4,if(cellx=”weekly”,3,if(etc etc)

Then just sum all of these in the cell you want to.

Stoner’s method is much cleaner, but my excel skills aren’t as good as his but that’s how I’d get the same result.

Here’s a way to make a drop down list I like to use that makes maintenance easier.

1. in a new tab create write a list of values e.g. in cells a1, a2, a4, a5.

2. select / highlight the list

3. Inset > Name > Define – enter a name e.g. mylist > OK.

4. Go to your data tab / cell – Data > Validation

5. Select List from 1st drop down

6. in Source enter =mylist > OK

Done. This way you can alter the drop downs easily on the lists tab.

Thanks everyone!

BB