Can this formula be condensed or simplified?

easybpw

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

easybpw said:
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

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

easybpw said:
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

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Active Member
Joined
Sep 30, 2003
Messages
407
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
easybpw said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top