|
| 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
April 9th, 2010, 11:10 AM
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
April 9th, 2010, 11:46 AM
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
April 9th, 2010, 01:59 PM
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
April 9th, 2010, 02:05 PM
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
April 9th, 2010, 03:33 PM
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
April 12th, 2010, 03:06 AM
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
April 12th, 2010, 03:07 AM
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
April 12th, 2010, 03:31 AM
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
April 12th, 2010, 03:51 AM
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
April 12th, 2010, 05:16 AM
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 - .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
