Sum with 3 conditions

[diepvic]

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.

 

[Steve Dunn]

=sum(offset($b$2:$f$2,match(a6,$a$3:$a$5,0),0))

 

[Luke M]

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))

 

[Steve Dunn]

Oops, missed that.

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))

 

[T. Valko]

One way...

=SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0))

 

[diepvic]

Thanks a billion

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]

Thanks a billion

One way...

=SUMIF(B1:F1,"YTD",INDEX(B3:F5,MATCH(A6,A3:A5,0),0))

--
Biff
Microsoft Excel MVP





.

 

[diepvic]

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

 

[Per Jessen]

Hi

Try this one:

=SUMPRODUCT(--(B1:I1="YTD")*--(A3:A5=A6)*--(RIGHT(B2:I2,1)=B6)*B3:I5)

Regards,
Per

 

[diepvic]

Thanks a bunch
This's really helpful

Hi

Try this one:

=SUMPRODUCT(--(B1:I1="YTD")*--(A3:A5=A6)*--(RIGHT(B2:I2,1)=B6)*B3:I5)

Regards,
Per



.