If a,b,c,d =2,3,4,5 in any order then e = 50

**azdub2** · 11-22-2016, 02:21 PM

If it was a simple as my title "If a,b,c,d =2,3,4,5 in any order then e = 50" I could use. IF(and(or(A=2,A=3,A=4,A=5),"50",OR(B=2,B=3,B=4,B=5),"50",OR(C=2,C=3,C=4,C=5),"50",OR(D=2,D=3,D=4,D=5),"50","No").

But I have several rows of like data and I will quickly run into error "the specified formula cannot be entered because it contains more values, references and/or names than are allowed in the current file format."

if 2,3,4,5 =50
if 1,2,3,4 = 75

A B C D
2 3 4 5
4 5 3 2
1 2 3 4
3 4 1 2
3 4 5 6

E would = 250

I hope that makes since...

**MrShorty** · 11-22-2016, 02:37 PM

Does it all have to occur in a single cell? Here's how I did it:

1) In F1, G1, H1, I1, enter the values to look for (2,3,4,5)
2) In F2, enter =sign(match(F$2,$A2:$D2,0)) and copy down and across. Note the use of relative and absolute references. This will return 1 for where the value is found, or an #N/A error where not found.
3) In E2, enter =IF(ISERROR(SUM(F2:I2)),0,50). If there are any errors in the sum range, then the iserror() function will catch that and tell us that one of the values was not found. if there are no errors, then we can return the value of 50 that you requested (note that I did not include the 50 in quotes so that it is the actual number 50. If you need this to be the "number stored as text" "50" text string, include the quotes). I'm sure someone who is more skilled at combining multiple single formulas into more complex single cell megaformulas can combine that into a single cell, but I tend to avoid that.

**queuesef** · 11-22-2016, 02:51 PM

I'm not following. I understand if it contains 2-5 it's result is 50, but why is 1-4 75? Also, where you have 'E would = 250' what does that refer to? And why is it 250.
Please take a moment to prepare the file with examples, so logic can be better understood. ( I suppose a formula could be written if any column contains 2 - 5 the result would be 50 and you could filter all the non50 amounts. Still lost on the calculation.

I hope this makes CENTS too (should be sense!)

**canapone** · 11-22-2016, 02:54 PM

Hi All,

first serie in A1:D1

In E1 to be copied down

=(COUNT(MATCH(NUM(IF(1,INDEX(ROW($2:$5),))),A1:D1,0))=4)*50+(COUNT(MATCH(NUM(IF(1,INDEX(ROW($1:$4),))),A1:D1,0))=4)*75

Regards

**canapone** · 11-22-2016, 03:11 PM

Hi again

if I have understood, a different approach and same numbers from

=(FREQUENCY(A1:D1,ROW($2:$5))=1)*50+(FREQUENCY(A1:D1,ROW($1:$4))=1)*75

Hope it helps

**queuesef** · 11-22-2016, 05:08 PM

Still not clear on what the formula/ relationship is if it is NOT the normal set of 2,3,4,5 BUT here's another attempt.
So I put in "look here" if it is NOT the normal set. You can add whatever formula is supposed to be used in place of "look here".

**azdub2** · 11-22-2016, 08:24 PM

I've added a file. Hope it helps.

**Sam Capricci** · 11-22-2016, 09:15 PM

This looks like it would work if I understand your needs correctly... in cell E1=IF(SUM(A1:D1)=14,50,IF(SUM(A1:D1)=10,75,0))
and in cell F1=IF(SUM(A1:D1)=14,20,IF(SUM(A1:D1)=10,15,0))
drag both downward.

**azdub2** · 11-22-2016, 10:15 PM

I'm not looking for if the sum of the cells = 14 then it should equal 50. Maybe using numbers was not a good representation. I change the labeling a bit maybe that will help

**canapone** · 11-23-2016, 01:46 AM

Hi in E1

Please Login or Register  to view this content.

in F1

Please Login or Register  to view this content.

----------------------------------------------

Another possible solutions

in E1

Please Login or Register  to view this content.

in F1

Please Login or Register  to view this content.

Hope it helps

**MrShorty** · 11-23-2016, 01:55 AM

How much did you dislike all the helper cells I proposed? I would probably build a "truth table" in a helper table like I show above. Then analyze that truth table. Perhaps some binary arithmetic or boolean algebra from there. Perhaps Follow that up with a lookup table to return the value you want for each truth table combination.

**azdub2** · 12-03-2016, 12:05 PM

MrShorty, I could not figure yours out so I do not know if it would of worked.

**azdub2** · 12-03-2016, 12:05 PM

Canapone, your countif worked.

**canapone** · 12-03-2016, 12:23 PM

Ciao,

thanks for sharing feedback.

If a,b,c,d =2,3,4,5 in any order then e = 50

LinkBack

Thread Tools

Rate This Thread

Display

If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Re: If a,b,c,d =2,3,4,5 in any order then e = 50

Thread Information

Users Browsing this Thread

Similar Threads

Find most common variation of product combination of with order type and order details

rolling order forecast - order planning excel combination of formulas

Copy all the datas if ORDER RECEIVED to order register sheet by date of order received

Copy all the datas if ORDER RECEIVED to order register sheet by date of order received

[SOLVED] Order Form to Summarise Order on another sheet with a Submit Order Button

How to connect purchase order and sell order with party ledger and daily day book

Bookmarks

Bookmarks

Posting Permissions