| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with 3 conditions
Hi,
I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
=sum(offset($b$2:$f$2,match(a6,$a$3:$a$5,0),0))
"diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
To expand on Steve Dunn's formula so that it only includes YTD months:
=SUMIF($B$1:$F$1,"YTD",OFFSET($B$2:$F$2,MATCH(A6,$ A$3:$A$5,0),0)) -- Best Regards, Luke M "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
Oops, missed that.
"Luke M" wrote in message ... To expand on Steve Dunn's formula so that it only includes YTD months: =SUMIF($B$1:$F$1,"YTD",OFFSET($B$2:$F$2,MATCH(A6,$ A$3:$A$5,0),0)) -- Best Regards, Luke M "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
One way...
=SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0 )) -- Biff Microsoft Excel MVP "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
Thanks a billion
"Luke M" wrote: To expand on Steve Dunn's formula so that it only includes YTD months: =SUMIF($B$1:$F$1,"YTD",OFFSET($B$2:$F$2,MATCH(A6,$ A$3:$A$5,0),0)) -- Best Regards, Luke M "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
Thanks a billion
"T. Valko" wrote: One way... =SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0 )) -- Biff Microsoft Excel MVP "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
What if I add one more condition like below:
YTD YTD YTD YTD YTD YTD Name Jan-X Feb-X Mar-X Apr-X Jan-Y Feb-Y Mar-Y Apr-Y A 23 34 35 2 3 4 5 8 B 2 12 111 3 12 14 0 12 C 142 11 45 23 33 121 23 0 Eg: I type "B" in Cell A6 and "X" in Cell B6. Then in cell C6 the formula will calculate total revenue that B earns from product X during the months marked "YTD". Pls help! Thanks a lot "T. Valko" wrote: One way... =SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0 )) -- Biff Microsoft Excel MVP "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
Hi
Try this one: =SUMPRODUCT(--(B1:I1="YTD")*--(A3:A5=A6)*--(RIGHT(B2:I2,1)=B6)*B3:I5) Regards, Per On 12 Apr., 04:31, diepvic wrote: What if I add one more condition like below: * * * * * * YTD * * * *YTD * * *YTD * * * * * * * * * YTD * * *YTD * * YTD *Name * *Jan-X * *Feb-X * Mar-X * *Apr-X * *Jan-Y * *Feb-Y * Mar-Y * Apr-Y *A * * * * *23 * * * * 34 * * * * *35 * * * * * 2 * * * *3 * * * * *4 * * * * * *5 * * * * 8 *B * * * * * *2 * * * * 12 * * * *111 * * * * * 3 * * * 12 * * * *14 * * * * * 0 * * * *12 *C * * * * 142 * * * *11 * * * * *45 * * * * 23 * * * 33 * * * *121 * * * * 23 * * * * 0 Eg: I type "B" in Cell A6 and "X" in Cell B6. Then in cell C6 the formula will calculate total revenue that B earns from product X during the months marked "YTD". Pls help! Thanks a lot "T. Valko" wrote: One way... =SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0 )) -- Biff Microsoft Excel MVP "diepvic" wrote in message ... Hi, I have a table as below * * * * * *YTD * YTD * *YTD Name * *Jan * *Feb * *Mar * *Apr * *May A * * * * *23 * * 34 * * * 35 * * *2 * * * *3 B * * * * * *2 * * 12 * * *111 * * 3 * * * 12 C * * * * 142 * *11 * * * *45 * *23 * * * 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum with 3 conditions
Thanks a bunch
This's really helpful "Per Jessen" wrote: Hi Try this one: =SUMPRODUCT(--(B1:I1="YTD")*--(A3:A5=A6)*--(RIGHT(B2:I2,1)=B6)*B3:I5) Regards, Per On 12 Apr., 04:31, diepvic wrote: What if I add one more condition like below: YTD YTD YTD YTD YTD YTD Name Jan-X Feb-X Mar-X Apr-X Jan-Y Feb-Y Mar-Y Apr-Y A 23 34 35 2 3 4 5 8 B 2 12 111 3 12 14 0 12 C 142 11 45 23 33 121 23 0 Eg: I type "B" in Cell A6 and "X" in Cell B6. Then in cell C6 the formula will calculate total revenue that B earns from product X during the months marked "YTD". Pls help! Thanks a lot "T. Valko" wrote: One way... =SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0 )) -- Biff Microsoft Excel MVP "diepvic" wrote in message ... Hi, I have a table as below YTD YTD YTD Name Jan Feb Mar Apr May A 23 34 35 2 3 B 2 12 111 3 12 C 142 11 45 23 33 Then I would like to set up a formula which can lookup the Name in the table above and then sum all the month marked "YTD". E.g: I type "B" in Cell A6. Then in cell B6 the formula will calculate total revenue that B earns from Jan to Mar (which are marked "YTD"). Pls advise what the formula should be. Thanks alot. .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditions | Excel Worksheet Functions | |||
| Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
| 2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
| shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
