Formula Help for three criterias

[markmol]

I was hoping someone could help me with a formula based on a certain scenario.

[TABLE="width: 417"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Organization[/TD]
[TD]Product Name[/TD]
[TD]Total $[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American o[/TD]
[TD]$6,564,132[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American Inc[/TD]
[TD]$3,647,234[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American CIB[/TD]
[TD]$443,245[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American Wash[/TD]
[TD]$523[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Pc[/TD]
[TD]$4,324,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 1[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 244[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 4567[/TD]
[TD]$432[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 95654[/TD]
[TD]$453,244[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin I6[/TD]
[TD]$45,555[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney5[/TD]
[TD]$45,665[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney 982[/TD]
[TD]$4,241,222[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney1[/TD]
[TD]$1,114[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney8[/TD]
[TD]$42,445[/TD]
[/TR]
</tbody>[/TABLE]



Based on the example above, I would like to write a formula that spits back the number 1 'Product Name' for each 'Organization' based on column 'Total $'.

So, in other words, IF column 'Organization'=American then give me back the largest value in column 'Total $' but return the 'Product Name' as the final output.

My initial swing at this formula was to combine a 'IF' function with the 'INDEX & MATCH' functions and 'large' function in order to give me back the top product name for each organization but I am not having much luck. Any help and insight is much appreciated. Thank you!

Please let me know if you need any clarification.

 

[markmol]

Ohhh yeah, that was just an example I provided with hypothetical numbers. I was more concerned about the formula and translated it to a bigger data set. Good catch though.

 

[Aladin Akyurek]

Ohhh yeah, that was just an example I provided with hypothetical numbers. I was more concerned about the formula and translated it to a bigger data set. Good catch though.

Since you do not want to see top 3 numbers in the output, the required set up becomes more demanding...
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Organization[/TD]
[TD] Product Name[/TD]
[TD] Product Type[/TD]
[TD] Total $[/TD]
[TD][/TD]
[TD] Top[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD] American[/TD]
[TD] American o[/TD]
[TD] A[/TD]
[TD] $6,564,132 [/TD]
[TD][/TD]
[TD][/TD]
[TD] american[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD] American[/TD]
[TD] American Inc[/TD]
[TD] A[/TD]
[TD] $3,647,234 [/TD]
[TD][/TD]
[TD][/TD]
[TD] A[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] American[/TD]
[TD] American o[/TD]
[TD] B[/TD]
[TD] $544,444 [/TD]
[TD][/TD]
[TD] Top Adjusted[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] American[/TD]
[TD] American Inc[/TD]
[TD] B[/TD]
[TD] $98,414,614 [/TD]
[TD][/TD]
[TD][/TD]
[TD] Product[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] American[/TD]
[TD] American CIB[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American o[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] American[/TD]
[TD] American Dune[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American Inc[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD] American[/TD]
[TD] American Wash[/TD]
[TD] B[/TD]
[TD] $6 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American CIB[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD] American[/TD]
[TD] American Pc[/TD]
[TD] C[/TD]
[TD] $4,324,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American Dune[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD] American[/TD]
[TD] Lincoln Z3[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] Lincoln Z3[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 1[/TD]
[TD] A[/TD]
[TD] $4,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 244[/TD]
[TD] A[/TD]
[TD] $4,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 4567[/TD]
[TD] A[/TD]
[TD] $432 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 95654[/TD]
[TD] A[/TD]
[TD] $453,244 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin I6[/TD]
[TD] A[/TD]
[TD] $45,555 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 1[/TD]
[TD] B[/TD]
[TD] $888,476 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 244[/TD]
[TD] B[/TD]
[TD] $6,476,126 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 4567[/TD]
[TD] B[/TD]
[TD] $4,362,167 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 95654[/TD]
[TD] B[/TD]
[TD] $461,633 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin I6[/TD]
[TD] B[/TD]
[TD] $5,456 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney 982[/TD]
[TD] A[/TD]
[TD] $4,241,222 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney1[/TD]
[TD] A[/TD]
[TD] $1,114 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney8[/TD]
[TD] A[/TD]
[TD] $42,445 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney 982[/TD]
[TD] B[/TD]
[TD] $5,566,113 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney1[/TD]
[TD] B[/TD]
[TD] $1,919,149,961 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney8[/TD]
[TD] B[/TD]
[TD] $41,456,546 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

1. Name the range in column A Org, the range in B Prod, the range in C Type, and the range in D Tdollar via the Name Box on the Formula Bar or via Formulas | Name Manager.

2. Define also Ivec via Formulas | Name Manager as referring to:

=ROW(Org)-ROW(INDEX(Org,1,1))+1

3. In G4 control+shift+enter, not just enter:

=SUM(IF(Org=G$2,IF(Type=G$3,IF(Tdollar>=LARGE(IF(Org=G$2,
    IF(Type=G$3,Tdollar)),MIN(G$1,COUNTIFS(Org,G$2,Type,G$3))),1))))

4. In G6 control+shift+enter and copy down:

=IF(ROWS(G$6:G6)<=G$4,INDEX(Prod,SMALL(IF(Org=G$2,IF(Type=G$3,
    IF(Tdollar=LARGE(IF(Org=G$2,IF(Type=G$3,Tdollar)),ROWS(G$6:G6)),Ivec))),
    SUM(IF(LARGE(IF(Org=G$2,IF(Type=G$3,Tdollar)),
    ROW($B$2:B2)-ROW($B$2)+1)=LARGE(IF(Org=G$2,IF(Type=G$3,Tdollar)),
    ROWS($G$6:G6)),1)))),"")

 

[markmol]

Taking a second look, I did find a hiccup with the formula. Not sure if this is what you were trying to explain to me
Aladin. The problem is, if all the total amounts are identical the formula takes the one that comes first; i believe this is also the logic for a vlookups if there are more than one hits. Here is an example of what I am talking about:

[TABLE="width: 373"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Organization[/TD]
[TD]Product Name[/TD]
[TD]Type[/TD]
[TD]Total $[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney8[/TD]
[TD]B[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney5[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney1[/TD]
[TD]B[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney 982[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin I6[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 95654[/TD]
[TD]B[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 4567[/TD]
[TD]C[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 244[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 1[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Wash[/TD]
[TD]B[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Pc[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American o[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Inc[/TD]
[TD]B[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American CIB[/TD]
[TD]A[/TD]
[TD]$1[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 284"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Criteria[/TD]
[TD]Product Type[/TD]
[TD]Product Name[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]A[/TD]
[TD]Stoney8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Stoney8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Stoney8[/TD]
[/TR]
</tbody>[/TABLE]


As you can see the results are Stoney8 for but the Criteria is Franklin.

Any ideas whats going on here?

 

[Aladin Akyurek]

See post #22.

 

[markmol]

I am intermediate at excel and never worked with full coding or really complex formulas like this so apologize for dumb questions but what is Ivec? Also, could you explain to me why does G1=3? And what does G4 formula do and why does G4=5?

Thanks

 

[Aladin Akyurek]

I am intermediate at excel and never worked with full coding or really complex formulas like this so apologize for dumb questions but what is Ivec? Also, could you explain to me why does G1=3? And what does G4 formula do and why does G4=5?

Thanks

You want a Top 3 list of products, right? That 3 is entered here in G1.

G4 tries to establish whether there is any value that is equal to the 3rd dollar value involving an organization and the product type that belongs to that organization.

Consider the following simple example:

jon, 7
dan, 3
joe, 7
frank, 5
linda, 5

Who has the highest/top 3 dollar values? I think you would say: jon, joe, frank, and linda. You see we have 4 in realty. G4 calculates those possible ties of the Nth value, where N = 3.

Ivec is the same as:

=ROW(A2:A26)-ROW(A2)+1 means {1,2,3,...,26}.

If you substitute Org for A2:A26, we would get:

=ROW(Org)-ROW(INDEX(Org,1,1))+1

where INDEX(Org,1,1) means the forst data cell of Org, that is, A2 here.

Hope this helps.

 

[markmol]

Wow! I did not even think of that scenario either, very insightful. Thanks for the input. For the purpose of what I am trying to build, it is actually okay if the 3rd product is a tie and which ever one comes first appears as the output. For this reason is it possible to skip the G4 cell step and if so how would that impact what you have written for G6 cell?

My thinking is to try to come up with the most simple possible formula that gets the job done.

 

[markmol]

Also, another reason i need to simplify is because i will be reusing this formula over a 1000 times in one workbook so I hope to trim it down to cut down calculation delays. Thanks again.

 

[Aladin Akyurek]

Also, another reason i need to simplify is because i will be reusing this formula over a 1000 times in one workbook so I hope to trim it down to cut down calculation delays. Thanks again.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td] Organization[/td][td] Product Name[/td][td] Product Type[/td][td] Total $[/td][td] Concat[/td][td] Top[/td][td] 3[/td][/tr]


[tr][td]

2​

[/td][td] American[/td][td] American o[/td][td] A[/td][td] $6,564,132 [/td][td] American|A[/td][td] Org[/td][td] american[/td][/tr]


[tr][td]

3​

[/td][td] American[/td][td] American Inc[/td][td] A[/td][td] $3,647,234 [/td][td] American|A[/td][td] Type[/td][td] A[/td][/tr]


[tr][td]

4​

[/td][td] American[/td][td] American o[/td][td] B[/td][td] $544,444 [/td][td] American|B[/td][td] Count[/td][td] 3[/td][/tr]


[tr][td]

5​

[/td][td] American[/td][td] American Inc[/td][td] B[/td][td] $98,414,614 [/td][td] American|B[/td][td][/td][td] Product[/td][/tr]


[tr][td]

6​

[/td][td] American[/td][td] American CIB[/td][td] A[/td][td] $443,245 [/td][td] American|A[/td][td][/td][td] American o[/td][/tr]


[tr][td]

7​

[/td][td] American[/td][td] American Dune[/td][td] A[/td][td] $443,245 [/td][td] American|A[/td][td][/td][td] American Inc[/td][/tr]


[tr][td]

8​

[/td][td] American[/td][td] American Wash[/td][td] B[/td][td] $6 [/td][td] American|B[/td][td][/td][td] American CIB[/td][/tr]


[tr][td]

9​

[/td][td] American[/td][td] American Pc[/td][td] C[/td][td] $4,324,523 [/td][td] American|C[/td][td][/td][td] [/td][/tr]


[tr][td]

10​

[/td][td] American[/td][td] Lincoln Z3[/td][td] A[/td][td] $443,245 [/td][td] American|A[/td][td][/td][td] [/td][/tr]


[tr][td]

11​

[/td][td] Franklin[/td][td] Franklin 1[/td][td] A[/td][td] $4,523 [/td][td] Franklin|A[/td][td][/td][td] [/td][/tr]


[tr][td]

12​

[/td][td] Franklin[/td][td] Franklin 244[/td][td] A[/td][td] $4,523 [/td][td] Franklin|A[/td][td][/td][td] [/td][/tr]


[tr][td]

13​

[/td][td] Franklin[/td][td] Franklin 4567[/td][td] A[/td][td] $432 [/td][td] Franklin|A[/td][td][/td][td] [/td][/tr]


[tr][td]

14​

[/td][td] Franklin[/td][td] Franklin 95654[/td][td] A[/td][td] $453,244 [/td][td] Franklin|A[/td][td][/td][td] [/td][/tr]


[tr][td]

15​

[/td][td] Franklin[/td][td] Franklin I6[/td][td] A[/td][td] $45,555 [/td][td] Franklin|A[/td][td][/td][td] [/td][/tr]


[tr][td]

16​

[/td][td] Franklin[/td][td] Franklin 1[/td][td] B[/td][td] $888,476 [/td][td] Franklin|B[/td][td][/td][td][/td][/tr]


[tr][td]

17​

[/td][td] Franklin[/td][td] Franklin 244[/td][td] B[/td][td] $6,476,126 [/td][td] Franklin|B[/td][td][/td][td][/td][/tr]


[tr][td]

18​

[/td][td] Franklin[/td][td] Franklin 4567[/td][td] B[/td][td] $4,362,167 [/td][td] Franklin|B[/td][td][/td][td][/td][/tr]


[tr][td]

19​

[/td][td] Franklin[/td][td] Franklin 95654[/td][td] B[/td][td] $461,633 [/td][td] Franklin|B[/td][td][/td][td][/td][/tr]


[tr][td]

20​

[/td][td] Franklin[/td][td] Franklin I6[/td][td] B[/td][td] $5,456 [/td][td] Franklin|B[/td][td][/td][td][/td][/tr]


[tr][td]

21​

[/td][td] Stoneblack[/td][td] Stoney 982[/td][td] A[/td][td] $4,241,222 [/td][td] Stoneblack|A[/td][td][/td][td][/td][/tr]


[tr][td]

22​

[/td][td] Stoneblack[/td][td] Stoney1[/td][td] A[/td][td] $1,114 [/td][td] Stoneblack|A[/td][td][/td][td][/td][/tr]


[tr][td]

23​

[/td][td] Stoneblack[/td][td] Stoney8[/td][td] A[/td][td] $42,445 [/td][td] Stoneblack|A[/td][td][/td][td][/td][/tr]


[tr][td]

24​

[/td][td] Stoneblack[/td][td] Stoney 982[/td][td] B[/td][td] $5,566,113 [/td][td] Stoneblack|B[/td][td][/td][td][/td][/tr]


[tr][td]

25​

[/td][td] Stoneblack[/td][td] Stoney1[/td][td] B[/td][td] $1,919,149,961 [/td][td] Stoneblack|B[/td][td][/td][td][/td][/tr]


[tr][td]

26​

[/td][td] Stoneblack[/td][td] Stoney8[/td][td] B[/td][td] $41,456,546 [/td][td] Stoneblack|B[/td][td][/td][td][/td][/tr]
[/table]

In E2 just enter and copy down:

=A2&"|"&C2<strike></strike>

E2:E26 is named OrgType.

In G4 just enter: [ modified ]

=MIN(G$1,COUNTIFS(OrgType,G$2&"|"&G$3))<strike></strike>

In G6 control+shift+enter, not just enter, and copy down:

=IF(ROWS(G$6:G6)<=G$4,INDEX(Prod,SMALL(IF(OrgType=G$2&"|"&G$3,
    IF(Tdollar=LARGE(IF(OrgType=G$2&"|"&G$3,Tdollar),ROWS(G$6:G6)),Ivec)),
    SUM(IF(LARGE(IF(OrgType=G$2&"|"&G$3,Tdollar),
    ROW($B$2:B2)-ROW($B$2)+1)=LARGE(IF(OrgType=G$2&"|"&G$3,Tdollar),
    ROWS($G$6:G6)),1)))),"")<strike></strike>

Note 1. This version is a set up for strictly Top N (i.e., the ties of the Nth value are ignored).

Note 2. The concatenation range in column E allows us to eliminate a few IF subexpressions, a state of affairs that improves the speed.

 

[markmol]

This is gold. Thanks so much for being patient with me and helping out, learned a ton just from this formula.