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: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:D,B2,Sheet1!E:E) +
sumif(sheet1!F:F,B2,Sheet1!G:G)

for example with B2 on Sheet2 containing Joe.

--
Regards,
Tom Ogilvy


"KathyDent1976" wrote in
message ...

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

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

Please Help!
-KLD


--
KathyDent1976
------------------------------------------------------------------------
KathyDent1976's Profile:
http://www.excelforum.com/member.php...o&userid=15013
View this thread: http://www.excelforum.com/showthread...hreadid=266312