Sum with 3 conditions

  • Thread starter Thread starter diepvic
  • Start date Start date
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.
 
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))
 
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))
Click to expand...
 
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



.
Click to expand...
 
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
 
Hi

Try this one:

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

Regards,
Per
 
Back
Top