How Do I assign a numeric value to a text entry?

[KathyDent1976]

Okay.. It's been a while since I've had my Excel courses. Here's th
scenario...

I have a worksheet that is tracking 'points' for a real estate sale
contest. Certain transactions performed with a property are 'worth
more points than others. Here's an example of what the columns loo
like:
------------ --------- -------------- ----------- -------------
-------------------- --------------
Address |-Sale-- |-Points: 1--|--Listing--|-Points: 2--|-In Hous
Sale--|--Points:3--
----------- -------- -------------- ----------- -------------
-------------------- --------------
123 A St.| Joe S. |------1------ |-Jane S.--|------2------|---Jil
S.------------|-----3-------
------------ -------- -------------- ----------- -------------
-------------------- --------------
456 B St.|---------|--------------| Sally T. |------2 ------|--Dav
R.---------|-----3--------
------------- --------- --------------- ------------ --------------
--------------------- --------------
My question is: How do I formulate the 'Points' cells to automaticall
populate with the correct number (1, 2, 3 points) just by typing tex
(the salesperson's name) into the column beside it? ALSO, how do
calculate all the points each salesperson has earned on anothe
worksheet if their name appears in different columns and row
throughout the worksheet (without doing =SUM ([and CTRL+clicking al
the different cells]) )??

Please Help!
-KL

 

[Tom Ogilvy]

in the cells under the points column

=If(cell to left = "","",1)

replace the 1 with 2 or 3 for the other columns.

so if cell C2

=if(B2="","",1)

assuming points are in C, E, G and names in B, D, F all on Sheet1.

=sumif(sheet1!B:B,"Joe",Sheet1!C:C) + sumif(sheet1!D,"Joe",Sheet1!E:E) +
sumif(sheet1!F:F,"Joe",Sheet1!G:G)

you can replace "Joe" with a cell reference on that sheet that contains Joe.
then you can put this formula next to a list of names and drag fill down the
column.

=sumif(sheet1!B:B,B2,Sheet1!C:C) + sumif(sheet1!D,B2,Sheet1!E:E) +
sumif(sheet1!F:F,B2,Sheet1!G:G)

for example with B2 on Sheet2 containing Joe.