LARGE VALUES ORDER LIKE SUMIFS

[bharath]

hi,

i want get results orderly max 1, max 2 & max 3 values like sumifs results
i attached file
please help...

thank you.

 

[Somendra Misra]

Hi Bharath,

Try below array formula in H2 and copy down and across.

=LARGE(IF($B$2:$B$22=$G2,$D$2:$D$22,0),COLUMNS($H2:H2))

Enter with Ctrl+Shift+Enter.

Regards,

 

[bharath]

thank you so much....

 

[Somendra Misra]

You are Welcome!!!

Regards,

 

[bharath]

It is not working with small formula ?
I just replace 'large' with 'small'

 

[Somendra Misra]

What is the result you are getting???

Regards,

 

[bharath]

zero i am getting.

 

[NARAYANK991]

Hi Bharath ,

You need to replace the 0 with a very large number.

See the file.

Narayan

 

[Somendra Misra]

Another method:

Just remove the ,0 from the formula.

Regards,

 

[bharath]

hi,

why do we change to large number instead of zero
there is no false i think, if false only false value will be selected, but there is no false in the table.

 

[Somendra Misra]

When putting 0, Small is always 0. SMALL function discard FALSE, so remove ,0 from formula.

Regards,

 

[NARAYANK991]

Hi Bharath ,

The formula is creating an array of values where the branch is say Branch 1 ; out of all the branch IDs , only a few will satisfy this condition.

Similarly , when you check for the branch equal to Branch 2 , there will be values which do not satisfy this condition.

Narayan

 

[bharath]

thanks a lot..

Bharath.