Formula Help for three criterias

[markmol]

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

Organization	Product Name	Total $
American	American o	$6,564,132
American	American Inc	$3,647,234
American	American CIB	$443,245
American	American Wash	$523
American	American Pc	$4,324,523
Franklin	Franklin 1	$4,523
Franklin	Franklin 244	$4,523
Franklin	Franklin 4567	$432
Franklin	Franklin 95654	$453,244
Franklin	Franklin I6	$45,555
Stoneblack	Stoney5	$45,665
Stoneblack	Stoney 982	$4,241,222
Stoneblack	Stoney1	$1,114
Stoneblack	Stoney8	$42,445

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

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

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Organization	Product Name	Product Type	Total $		Top	3
2​	American	American o	A	$6,564,132			american
3​	American	American Inc	A	$3,647,234			A
4​	American	American o	B	$544,444		Top Adjusted	5
5​	American	American Inc	B	$98,414,614			Product
6​	American	American CIB	A	$443,245			American o
7​	American	American Dune	A	$443,245			American Inc
8​	American	American Wash	B	$6			American CIB
9​	American	American Pc	C	$4,324,523			American Dune
10​	American	Lincoln Z3	A	$443,245			Lincoln Z3
11​	Franklin	Franklin 1	A	$4,523
12​	Franklin	Franklin 244	A	$4,523
13​	Franklin	Franklin 4567	A	$432
14​	Franklin	Franklin 95654	A	$453,244
15​	Franklin	Franklin I6	A	$45,555
16​	Franklin	Franklin 1	B	$888,476
17​	Franklin	Franklin 244	B	$6,476,126
18​	Franklin	Franklin 4567	B	$4,362,167
19​	Franklin	Franklin 95654	B	$461,633
20​	Franklin	Franklin I6	B	$5,456
21​	Stoneblack	Stoney 982	A	$4,241,222
22​	Stoneblack	Stoney1	A	$1,114
23​	Stoneblack	Stoney8	A	$42,445
24​	Stoneblack	Stoney 982	B	$5,566,113
25​	Stoneblack	Stoney1	B	$1,919,149,961
26​	Stoneblack	Stoney8	B	$41,456,546

<tbody>
</tbody>

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:

Organization	Product Name	Type	Total $
Stoneblack	Stoney8	B	$1
Stoneblack	Stoney5	A	$1
Stoneblack	Stoney1	B	$1
Stoneblack	Stoney 982	A	$1
Franklin	Franklin I6	A	$1
Franklin	Franklin 95654	B	$1
Franklin	Franklin 4567	C	$1
Franklin	Franklin 244	A	$1
Franklin	Franklin 1	A	$1
American	American Wash	B	$1
American	American Pc	A	$1
American	American o	A	$1
American	American Inc	B	$1
American	American CIB	A	$1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Criteria	Product Type	Product Name
Franklin	A	Stoney8
		Stoney8
		Stoney8

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Organization	Product Name	Product Type	Total $	Concat	Top	3
2​	American	American o	A	$6,564,132	American|A	Org	american
3​	American	American Inc	A	$3,647,234	American|A	Type	A
4​	American	American o	B	$544,444	American|B	Count	3
5​	American	American Inc	B	$98,414,614	American|B		Product
6​	American	American CIB	A	$443,245	American|A		American o
7​	American	American Dune	A	$443,245	American|A		American Inc
8​	American	American Wash	B	$6	American|B		American CIB
9​	American	American Pc	C	$4,324,523	American|C
10​	American	Lincoln Z3	A	$443,245	American|A
11​	Franklin	Franklin 1	A	$4,523	Franklin|A
12​	Franklin	Franklin 244	A	$4,523	Franklin|A
13​	Franklin	Franklin 4567	A	$432	Franklin|A
14​	Franklin	Franklin 95654	A	$453,244	Franklin|A
15​	Franklin	Franklin I6	A	$45,555	Franklin|A
16​	Franklin	Franklin 1	B	$888,476	Franklin|B
17​	Franklin	Franklin 244	B	$6,476,126	Franklin|B
18​	Franklin	Franklin 4567	B	$4,362,167	Franklin|B
19​	Franklin	Franklin 95654	B	$461,633	Franklin|B
20​	Franklin	Franklin I6	B	$5,456	Franklin|B
21​	Stoneblack	Stoney 982	A	$4,241,222	Stoneblack|A
22​	Stoneblack	Stoney1	A	$1,114	Stoneblack|A
23​	Stoneblack	Stoney8	A	$42,445	Stoneblack|A
24​	Stoneblack	Stoney 982	B	$5,566,113	Stoneblack|B
25​	Stoneblack	Stoney1	B	$1,919,149,961	Stoneblack|B
26​	Stoneblack	Stoney8	B	$41,456,546	Stoneblack|B

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.