Large IF, Match 1 or more criterias in multiple columns

[Mange08]

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.



BR
Magnus

 

[r1c1]

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

 

[NARAYANK991]

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

 

[Mange08]

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

 

[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