Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Date to Work Week... | Excel Discussion (Misc queries) | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
Tab key don't work in unprotected cells in a protected sheet | Excel Discussion (Misc queries) | |||
Some Excel links don't work | Excel Discussion (Misc queries) | |||
How to get saved old saved work that was saved over? | Excel Discussion (Misc queries) |