Can this formula be condensed or simplified?

[easybpw]

I have this formula that gives me the correct results I am looking for. However I am hoping there is an easier formula or one that condenses what I have and simplifies it. Here is my formula:

=SUMPRODUCT(--(B13476="CB"),--(D13476="N")--(D13476="Y"),W13476*8)+SUMPRODUCT(--(B13476="S"),--(D13476="N")--(D13476="Y"),W13476*8)+SUMPRODUCT(--(B13476="DE"),--(D13476="N"),W13476*12)+SUMPRODUCT(--(B13476="DE"),--(D13476="Y"),W13476*8)+SUMPRODUCT(--(B13476="DT"),--(D13476="N"),W13476*12)+SUMPRODUCT(--(B13476="DT"),--(D13476="Y"),W13476*8)+SUMPRODUCT(--(B13476="IB"),--(D13476="N")--(D13476="Y"),W13476*10)+SUMPRODUCT(--(B13476="OB"),--(D13476="N"),W13476*12)+SUMPRODUCT(--(B13476="OB"),--(D13476="Y"),W13476*10)


The formula looks at cells in columns B & D and if the answers are correct it multiplies column W by a certain figure. In certain parts of the formula the cell in column D can either be a Y or N. What I am hoping to find is an easier, more simplified formula than this. Is this possible or do I have it the best it can be?

Thanks for all the help. I hope I am clear enough for you.

Bill

 

[Aladin Akyurek]

Bill,

Care to spell out what the bit...

SUMPRODUCT(--(B13476="CB"),--(D13476="N")--(D13476="Y"),W13476*8)

means - in particular the sub-bit --(D13476="N")--(D13476="Y")?

 

[Oaktree]

How about:

=W13476*vlookup(B13476&"_"&D13476,{"CB_N",8; "CB_Y", 8; "S_Y", 8;"S_N",8;"DE_N",12; "DE_Y",8; "DT_N",12; "DT_Y",8; "IB_Y",10; "IB_N",10; "OB_N",12; "OB_Y",10},2,0)

 

[easybpw]

Aladin,

The reason for that part of the formula is that column D will always have either a N or Y in it. So I wanted the formula to recognize that and then if all criteria are met to calculate column W. Does that make sense?

Bill

 

[Aladin Akyurek]

Aladin,

The reason for that part of the formula is that column D will always have either a N or Y in it. So I wanted the formula to recognize that and then if all criteria are met to calculate column W. Does that make sense?

Bill

I guessed that much, but that sub-bit is now wel-formed. Moreover, invoking SumProduct with conditionals that involve single-cell ranges is often unnecessary. The following is maybe what you're looking for:

=(W13476*ISNUMBER(MATCH(B13476,{"CB","S","DE","DT","IB","OB"},0)))*((D13476="Y")*8+(D13476="N")*12)

which is similar in direction to what Oaktree suggests.

 

[easybpw]

Aladin,

That is what I am looking for. Seems simple once it is shown to me. Much harder coming up with it on my own. I'll try to add to this formula on my own.

Thanks for the help.

Bill

 

[Aladin Akyurek]

Aladin,

That is what I am looking for. Seems simple once it is shown to me. Much harder coming up with it on my own. I'll try to add to this formula on my own.

Thanks for the help.

Bill

You're welcome.

A small edit:

The "now" in my previous post should be read as "not".

Hope you don't mind my coming back to it.

 

[easybpw]

Aladin,

Any bit of information I can absorb from you is helpful. I have appreciated and learned a lot from you. Thanks again.

Bill

 

[easybpw]

Aladin,

I was wrong. The formula you provided is great but not correct. If you look at my example above, for the "IB" the multiplier in W13476 is 10. You have my multipliers being 8 and 12. That is true for the rest but not for "IB". Do you see what I mean?

Bill

 

[Aladin Akyurek]

Aladin,

I was wrong. The formula you provided is great but not correct. If you look at my example above, for the "IB" the multiplier in W13476 is 10. You have my multipliers being 8 and 12. That is true for the rest but not for "IB". Do you see what I mean?

Bill

I missed that. I think you should switch the VLOOKUP formula Oaktree suggests.