Sumif right one to use ???

[Simbo]

Hello all

need help with something

I run a nascar pool for freinds and co-works and instead of hand
writing like i have been i would like to have excel due the math for
me.

Here is what i got so far

Sheet 1
Place that drivers came that week
A...........B.........C.........D.........E..........ECT
Place.. Points.. Week1 ... week2 ..... week3
1st ..... 20 ........ 8 ........... 21
2nd ..... 19 ...... 18 .......... 29
3rd ..... 18 ........ 9 ........... 8

Sheet 2

A..............B..............C..............D..............E
Player .. Picks ... Points
Week1 ... 2 ..........
=SUMIF(Sheet1!C2:B4,B2,Sheet1!B2:B4) =0
.............. 8 .........
=SUMIF(Sheet1!C2:B4,B3,Sheet1!B2:B4) =20
.............. 21 ........
=SUMIF(Sheet1!C2:B4,B4,Sheet1!B2:B4) =0


This Formula works fine but i need to do for 15 to 20 players, i wanted
to have all players on one sheet but when i try to copy the formula to
other cells it changes some of the cell references.

I really don't want to edit every cell there will be 6 picks per week
for 37 weeks at 20 players thats over 4000 cells ,


Any help would be great

 

[Anne Troy]

To NOT change cell references, see how you can used named ranges:
http://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
(specifically see where I show "markup" and how you could likely use the
driver's names there)
You should probably also learn about Absolute cell referencing:
http://www.cpearson.com/excel/relative.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[daddylonglegs]

You can use the $ sign to fix the row references. If you use

=SUMIF(Sheet1!C$2:B$4,B2,Sheet1!B$2:B$4)

then when you copy formula down the B2 changes to B3 but the other
refernces stay the same

 

[Bob Phillips]

Are you sure about that formula. It doesn't seem right to me, C2:B4 would
get changed by Excel to B2:C4

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)