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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top