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
|
|||
|
|||
Formula Needed : Sum on Conditions
Row A B C…….columns
1 FTB YRT STX 2 USA CAN UAE 3 33 66 33 4 STX FTB YRT 5 UAE USA CAN 6 44 88 69 7 YRT STX FTB 8 CAN UAE USA 9 66 77 88 Hi all, I want smallest formula in cell D1 which should show SUM of figures which are in Range("A1:C9") (as shown above) after working out something like this (see below) =SUM(A1:C9="FTB")+(A1:C9="USA)+(A1:C9) In other words I want formula to find "FTB" in given range and then if there is "USA" on row below then sum the figure in row below the "USA". I hope I was able to explain my question. Please can any friend can help. |
#2
|
|||
|
|||
Formula Needed : Sum on Conditions
Put this in D1:
=SUMPRODUCT((A1:C7="FTB")*(A2:C8="USA"),(A3:C9)) and it will return 209. Change it to STX and UAE and you get 154. Hope ths helps. Pete On Jan 27, 2:58*pm, K wrote: Row *A * * * *B * * * *C…….columns 1 * * * FTB * YRT * STX 2 * * * USA * CAN * UAE 3 * * * 33 * * *66 * * *33 4 * * * STX * FTB * YRT 5 * * * UAE *USA * CAN 6 * * * 44 * * *88 * * * 69 7 * * * YRT * STX * FTB 8 * * * CAN * UAE * USA 9 * * * 66 * * *77 * * * 88 Hi all, *I want smallest formula in cell D1 which should show SUM of figures which are in Range("A1:C9") (as shown above) after working out something like this (see below) *=SUM(A1:C9="FTB")+(A1:C9="USA)+(A1:C9) In other words I want formula to find "FTB" in given range and then if there is "USA" on row below then sum the figure in row below the "USA". *I hope I was able to explain my question. *Please can any friend can help. |
#3
|
|||
|
|||
Formula Needed : Sum on Conditions
On Jan 27, 3:08*pm, Pete_UK wrote:
Put this in D1: =SUMPRODUCT((A1:C7="FTB")*(A2:C8="USA"),(A3:C9)) and it will return 209. Change it to STX and UAE and you get 154. Hope ths helps. Pete On Jan 27, 2:58*pm, K wrote: Row *A * * * *B * * * *C…….columns 1 * * * FTB * YRT * STX 2 * * * USA * CAN * UAE 3 * * * 33 * * *66 * * *33 4 * * * STX * FTB * YRT 5 * * * UAE *USA * CAN 6 * * * 44 * * *88 * * * 69 7 * * * YRT * STX * FTB 8 * * * CAN * UAE * USA 9 * * * 66 * * *77 * * * 88 Hi all, *I want smallest formula in cell D1 which should show SUM of figures which are in Range("A1:C9") (as shown above) after working out something like this (see below) *=SUM(A1:C9="FTB")+(A1:C9="USA)+(A1:C9) In other words I want formula to find "FTB" in given range and then if there is "USA" on row below then sum the figure in row below the "USA". *I hope I was able to explain my question. *Please can any friend can help.- Hide quoted text - - Show quoted text - Thanks lot Pete |
#4
|
|||
|
|||
Formula Needed : Sum on Conditions
You're welcome - thanks for taking the trouble to feed back.
Pete On Jan 27, 3:43*pm, K wrote: Thanks lot Pete |
Thread Tools | |
Display Modes | |
|
|