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

Large IF, Match 1 or more criterias in multiple columns

Mange08

New Member
Hi,

I'm trying to figure out how to use 1-3 criterias in a Large formula,
criterias show up in 3 different named ranges and the formula needs to figure out which named range should be included in the calculation.
I´m thinking a nested IF function maybe work but i can´t solve it.

upload_2015-11-26_22-16-35.png

BR
Magnus
 

Attachments

  • Large_Multiple conditions and columns.xlsx
    34.4 KB · Views: 14
@Mange08 I think you can solve this with a helper column next to original data table. You may be able to solve this with single formula, but I can't figure out a simple solution yet.

Please find the attached workbook.
 

Attachments

  • Large_Multiple conditions and columns.xlsx
    9.9 KB · Views: 9
Hi Magnus ,

I think your problem definition needs some clarification.

1. When you specify 3 criteria , such as in columns G , H and I , are the criteria to be ANDed or ORed ?

2. When each column has multiple data items , I assume that the multiple values are to be ORed.

Thus given that you have the values Cust1 and Cust2 in column G , a and c in column H , and dfg in column I , the final condition would be :

(Column B = Cust1) OR (Column B = Cust2) OR (Column C = a) OR (Column C = c) OR (Column D = dfg)

Is this correct ?

In which case , the answer to the LARGE question for all 3 criteria would be the answer to the question which is the largest value satisfying the above condition ?

The answer then should be 4962 ; is this correct ?

Narayan
 
Hi, and thanks for your feedback.

r1c1: You solution works out, why didn´t i think of a helper column :(
It´s really a simple and solid solution.

Br
Mange08
 
Hi Magnus ,

I think your problem definition needs some clarification.

1. When you specify 3 criteria , such as in columns G , H and I , are the criteria to be ANDed or ORed ? Both

2. When each column has multiple data items , I assume that the multiple values are to be ORed. ANDed, because what i do want to use it for is to create a top10 based on what different product groups a user have been choosen in another sheet. i didn´t like how Top10 was presented in PivotTable.

Thus given that you have the values Cust1 and Cust2 in column G , a and c in column H , and dfg in column I , the final condition would be :

(Column B = Cust1) OR (Column B = Cust2) OR (Column C = a) OR (Column C = c) OR (Column D = dfg)

Is this correct ?
I was thinking more of AND/OR.
If namerange1 has no values it would then calculate on namedrange 2 and 3.
And if it has values only in namedrange 3 it would calculate on that only. Basically r1c1´s solution works out as i want it too but if you can solve it in a formula it would be rather cool.


In which case , the answer to the LARGE question for all 3 criteria would be the answer to the question which is the largest value satisfying the above condition ?

The answer then should be 4962 ; is this correct ?
Yes, correct

Narayan

Thanks for helping!
Mange08
 
Back
Top