Cant get totals to work with IF statements

[spelingchampeon]

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 D342 (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 D342, 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(D342), 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.

 

[Vasant Nanavati]

I'm really not sure I understand, but try:

=COUNTIF(D342,1)*3-COUNTIF(D342,2)-COUNTIF(D342,3)-COUNTIF(D342,4)

--

Vasant



"spelingchampeon"

 

[Roger Govier]

Hi

One way would be the array formula
{=SUM((D342=1)*3+(D342>1)*-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(D342,1)*3 + COUNTIF(D342,">"&1)*-1

Regards

Roger Govier

 

[Lilliabeth]

Are you putting the "place" (1,2,3, or 4) in the same cell as the I
function, thereby overwriting it?

If you put the "place" in column D, then put the IF functions in colum
E and then sum column E.

in cell E3:
=IF(D3=1,3,-1)

If the "place" is 1, then put a 3 in here, otherwise put -1 in here

 

[spelingchampeon]

Thanks for your help. The condensed COUNTIF works great. Thanks again!