Cant get totals to work with IF statements
 

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.


--
spelingchampeon
------------------------------------------------------------------------
spelingchampeon's Profile: http://www.excelforum.com/member.php...o&userid=27949
View this thread: http://www.excelforum.com/showthread...hreadid=474534

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

=COUNTIF(D3:D42,1)*3-COUNTIF(D3:D42,2)-COUNTIF(D3:D42,3)-COUNTIF(D3:D42,4)

--

Vasant



"spelingchampeon"
<spelingchampeon.1wm46c_1128823504.9635@excelfor um-nospam.com wrote in
message news:spelingchampeon.1wm46c_1128823504.9635@excelf orum-nospam.com...

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.


--
spelingchampeon
------------------------------------------------------------------------
spelingchampeon's Profile:
http://www.excelforum.com/member.php...o&userid=27949
View this thread: http://www.excelforum.com/showthread...hreadid=474534

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.

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

If you put the "place" in column D, then put the IF functions in column
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.


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=474534

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


--
spelingchampeon
------------------------------------------------------------------------
spelingchampeon's Profile: http://www.excelforum.com/member.php...o&userid=27949
View this thread: http://www.excelforum.com/showthread...hreadid=474534