| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 47985 | 2004-08-11 04:50:00 | Excel formula | bpt1 (419) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 260411 | 2004-08-11 04:50:00 | Can anyone help with a formula? In Excel I want to sum a column of numbers when conditions in 2 adjacent columns apply. E.g. sum(a2:a30),if(b2:b30="T")and if(c2:c30,"<5") |
bpt1 (419) | ||
| 260412 | 2004-08-11 06:00:00 | Hi bpt1 Firstly, I must give credit to rugila for teaching me about array formulas in a previous thread, but this is what you are looking for : Enter the following formula : =SUM(IF(B2:B30="T",IF(C2:C30<5,A2:A30))) BUT DO NOT click the tick or DO NOT press Enter but instead press CTRL-SHIFT-ENTER together. If you don’t press the CTRL-SHIFT you just get a #VALUE!, if you do you should get the right answer. This is an array formula which can only be entered in this way. I have tried it and it works. All credit to rugila for this one - I lifted part of the answer from his post on this thread (pressf1.pcworld.co.nz). Andrew |
andrew93 (249) | ||
| 260413 | 2004-08-11 06:19:00 | What are you wanting to do if any of the conditions are false? Andrew's formula will successfully add all values where the two conditions in the same row are true, leaving out the values which have corresponding false conditions. If you want to run the calculation only when ALL conditions are true, this may help (again, use ctrl-shift-enter to validate): =IF(AND(B2:B30="T",C2:C30<5),SUM(A2:A30),"XX") where XX is the text displayed if any conditions are false (use just "" to display a blank cell). |
Spartacus (3313) | ||
| 260414 | 2004-08-11 09:18:00 | Have you tested your formula Spartacus? It doesn't work - it returns "XX" even if there are some valid entries. | andrew93 (249) | ||
| 260415 | 2004-08-11 10:49:00 | Andrew93: Thanks for your overly generous comments. I'm pleased to be able to be of some help. Spartacus: I don't quite follow what you are getting at. With Andrew93's function the value in column A is added if and only if both of the corresponding conditions in columns B and C are true. If either of the B or C conditions (includes both) is not true then the value in column A is not added. This seems to me exactly what was asked for and is just what the function does. I don't see any other possibilities in the original problem as posed. |
rugila (214) | ||
| 260416 | 2004-08-11 11:23:00 | Hi, I think this should do it. =SUMPRODUCT(--(B2:B30="T")*(C2:C30<5),A2:A30) |
parry (27) | ||
| 260417 | 2004-08-11 12:46:00 | Now that’s interesting Parry. I never realised before that Excel treats the text words true and false as one and zero when doing arithmetical operations. For example the function =true+true gives the answer 2, etc. I always used expressions like =if(true,1,0) +if(true,1,0) etc. which is the same thing but clumsier. Even works with things like =True^false and sqrt(true) I guess we all learn as we go along, as we all should. Don’t really see why you used the double negative tho’, except as a self-neutralising spacer to make it easier to read. Makes yours a bit clumsier than need be too . |
rugila (214) | ||
| 260418 | 2004-08-11 12:51:00 | ditto - i was puzzled by the double negative too - what's with it parry? cool funciton nonetheless. BTW rugila - i was just giving credit where it was due. :D |
andrew93 (249) | ||
| 260419 | 2004-08-11 20:14:00 | Hi Rugila & Andrew. Yes Rugila, your right I didnt need the double negative after all. The double negative is used to coerce a text value into 1's and 0's and so I have a habit of using this as some formulas fail with textual components. Aladin Akyurek has some info on Sum Product here (www.mrexcel.com). |
parry (27) | ||
| 260420 | 2004-08-11 21:32:00 | >it returns "XX" even if there are some valid entries. Like I said, it returns a valid result ONLY when ALL conditions are true - that is, all B2:B30 = T, and all C2:C30 < 5. Again, it depends on the situation as to whether this is necessary - clearly in this case it isn't... :) |
Spartacus (3313) | ||
| 1 2 3 | |||||