SUMIF LARGE and VLOOKUP

[Mange08]

Hi,

Ok, so my idea is to show Top3 in H6:H8 with values in I column.
There can be duplicate values in column B so i have to summarize them first and the pick the 3 Largest from there.
It should get the values based on what criteria i choose in I4, so for this example i would like Top3 from sales.
I guess there should be an Lookup or Index/Match there as well so i can get which Artikel got the value listed in I6:I8.

I have fooled around with Sumifs, Large, Index, Match but i can't figure it out.
I think there is a simple solution from any of you Ninjas.

 

[Mike H..]

Hi,

Ok, so my idea is to show Top3 in H6:H8 with values in I column.
There can be duplicate values in column B so i have to summarize them first and the pick the 3 Largest from there.
It should get the values based on what criteria i choose in I4, so for this example i would like Top3 from sales.
I guess there should be an Lookup or Index/Match there as well so i can get which Artikel got the value listed in I6:I8.

I have fooled around with Sumifs, Large, Index, Match but i can't figure it out.
I think there is a simple solution from any of you Ninjas.

View attachment 12193

Hi,

Check out your workbook.

Thinking about it there may be the possibility of a tie so in the second workbook the formula will resolve ties.

 

[Mange08]

Hi,

Thanks for your suggestion.

I think i might expressed my query unclear, so i try again.
I would like the LARGE formula to be calculated on the sums of each "Artikel"
E.g in column B 1006366 shows up three times so the result should be 918+6018+2475=9411, resulting in Top1 position.
Also if i type "quantity" in I4 it should calculate and give Top3 for that column instead.

Thanks in advance.
Br
Magnus

 

[Somendra Misra]

Hi Magnus,

See your file, I had included two name range and a helper sheet. Also note if you extend the LARGE formula further down along with wrapping in IFERROR you can also get duplicates.

Regards,

 

[Mange08]

Hi Magnus,

See your file, I had included two name range and a helper sheet. Also note if you extend the LARGE formula further down along with wrapping in IFERROR you can also get duplicates.

Regards,

As usual a very good solution.
It worked very good in my master workbook =)

Thank you!

 

[Somendra Misra]

Glad you solved it, thanks for the feedback & welcome back.

Regards,

 

[Mange08]

Hi again,

What if i want to add a second criteria in column F,
And only sum if column F matches this criteria and the one in I4

Where do i put the additional IF?

Br
Magnus

 

[Somendra Misra]

@Mange08

Glad to help you again but can you upload what you are saying in the file you uploaded earlier.

Regards,

 

[Mange08]

Thanks for helping.
So i added a Criteria in column F and criteria is set in J4.
I would like Top3 to be based on two criterias, I4 and J4.

Is it possible to alter the Array formula in Sheet2 C column to accomplish this?
That would leave out duplicates as well.

Br
Magnus

 

[Somendra Misra]

See the file. If this is OK.

Regards,

 

[Mange08]

Worked out perfectly,
thanks again

BR
Magnus

 

[Somendra Misra]

Thanks for the feedback Magnus, you are welcome back here again.

Regards,