• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use Large If formula in multiple combinations

ThrottleWorks

ThrottleWorks

Excel Ninja
Column A - Name

Column B - Amount


I have applied validation in Range V3, list is names in column A.

Range V4 to V13, I have applied LARGE & IF formula.

It gives me large no in serial order for value present in range V3.


I have multiple departments in column C for example North, East, West, South.

I have all these dept in range W3 to AD3.

Now I want to apply a formula in range w4 to AD13.


For example I will take range W4 to W13.


Range V3 has a value “Sachin” ,so range V4 will give me large no 1 from Column B for Sachin.


I want a formula in range W4 which will give me large no 1 for Name Sachin & Dept “North” (value in W3) and so on.


Can anyone help me in this please.
 
Having a hard time understanding your layout. Could you post a small sample of what it looks like, and what you are hoping to make it look like? If you copy and paste from your worksheet, enclose the data in backticks (`), not single apostrophes, to preserve the spacing.
 
Sorry for the confusion.


I will try to explain it better.


Column A Name (example - Sachin)

Column B Region (North,east,west,south)

Column C Amount (200,400,600,800) and so on


Columnd D - I need formula here


The formula will check following things.


Name = Sachin & Region = North if this is true get the largest amount for this combination


I have following combination


Sachin North 100

Sachin North 200

Sachin North 300

Sachin North 400


the result of the formula should be 400,


Thanks a lot for the help, sorry for the confusion.
 
Thanks, that helps clear things up. I think this will do it.

=SUMPRODUCT(MAX((A2:A10="Sachin")*(B2:B10="North")*(C2:C10)))


Obviously you can replace the text strings with cell references to suit your layout.
 
Sir thanks a lot for the help but my mistake, I want to use Large formula not the max.


I am trying to list top 10 values using large formula.


I should have mentioned it, could you please help me if yuou have time.


I tried editing this formula but was not able to do it.
 
Assuming you want to list all of them, something like this:

=SUMPRODUCT(LARGE(($A$2:$A$10="Sachin")*($B$2:$B$10="North")*($C$2:$C$10),ROW(A1)))


The first 2 arrays in the LARGE function create logic/boolean arrays filled with 1's and 0's, which we then multiply against the values array (in col C). Only rows where the first 2 arrays have a 1 (aka, "true") will return a value then. The LARGE function then takes that array and uses the last argument (being generated by the ROW function to determine which Nth number to pull. If you copy this formula to 1 cell down, it will pull the 2nd largest number, and 1 more cell will pull the 3rd largest, and so on.
 
You must log in or register to reply here.
Back
Top