If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 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. -- 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(D342,1)*3-COUNTIF(D342,2)-COUNTIF(D342,3)-COUNTIF(D342,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 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. -- 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((D342=1)*3+(D3421)*-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 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 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. |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab Query Help | Becks | New Users | 17 | October 11th, 2005 08:31 PM |
how do I create a work order form | cher952 | Using Forms | 1 | March 14th, 2005 05:09 PM |
Problem With Nested Joins | Don | Running & Setting Up Queries | 8 | February 26th, 2005 09:04 PM |
Work Areas in a database | LMB | New Users | 3 | October 4th, 2004 11:31 PM |
"Work Offline" Menu and "Exit" Menu | MSHUME | General Discussion | 1 | September 27th, 2004 09:17 PM |