# Sum with 3 conditions

D

#### 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.

S

#### Steve Dunn

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

L

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

S

#### Steve Dunn

Oops, missed that.

Luke M said:
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

#### T. Valko

One way...

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

D

#### diepvic

Thanks a billion

Luke M said:
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

.

D

#### diepvic

Thanks a billion

T. Valko said:
One way...

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

--
Biff
Microsoft Excel MVP

.

D

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

P

#### Per Jessen

Hi

Try this one:

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

Regards,
Per

D

#### diepvic

Thanks a bunch

Per Jessen said:
Hi

Try this one:

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

Regards,
Per

.