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.

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.

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.

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.

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.

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.


.

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.


.

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.


.

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 -

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 -

.