Hi

One way would be the array formula
{=SUM((D3:D42=1)*3+(D3:D421)*-1)}

Array formulas are committed with Ctrl+Shift+Enter, not just Enter. Do
not use the curly braces { } in the formula yourself, Excel will insert
them when you press Ctrl+Shift+Enter.

Vasant has given you a non-array solution, which could be slightly
shortened to
=COUNTIF(D3:D42,1)*3 + COUNTIF(D3:D42,""&1)*-1

Regards

Roger Govier



spelingchampeon wrote:

I have a situation where I want cells to total up each week. I'll try to
set it up as best as possible:

For my explaination, I will use 1 of 4 people involved.. lets say his
name is Burt. Burt's cell spread is D3:D42 (each successive cell is for
the week), so the spreadsheet spans 40 weeks.

Each week, I will enter either a 1, 2, 3 or 4 ( Burt's weekly
placings)

Each week, the person with the most points will have a 1 entered (1st
place for that week), and will be awarded 3 points (+3). The ones who
place 2nd, 3rd or 4th will be docked 1 (-1)

In the cell range D3:D42, I tried to do an =IF statement. When I click
on the cell range, and then select Insert-Function, I choose IF.. then
for the "logical_test", I put the range in and the number 1 (for 1st
place). for "value_if_true" +3
"value_if_false" -1

OK, lets say for the 1st week, in the D3 cell I put 1 for 1st place
that week. I then want the value (+3) to be totalled in a totally
separate cell off to the side of my weekly totals. I went to that cell
and inserted a function: =SUM(D3:D42), hoping the totals would add up
automatically. It never works.. when I put a 1 in, it only adds a 1 to
the total, and if I put a 2, 3 or 4 in, the total adds whatever digit I
put in the cell, and the minus' wont work either. DOH!

I'm new to excel.. and thusly stumped! Any help would be greatly
appreciated. Thanks.