Large with multiple if and All functions

[notimepelican]

Hey all

I am trying to use the large formula to return values based on multiple criterea.I can get this to work using the formula below without issues

{=LARGE(IF(Raw!B1:B55027='Top Products'!$E$6,IF(Raw!C1:C55027='Top Products'!$E$3,IF(Raw!M1:M55027='Top Products'!$E$4,IF(Raw!N1:N55027='Top Products'!$E$5,IF(Raw!A1:A55027='Top Products'!$G$2,IF(Raw!P1:P55027='Top Products'!$F$2,Raw!J1:J55027)))))),C12)}

But now the issue is that the data is fed from drop downs and I want to use an "All" option for three of these drop downs. I have the below but cannot get it to work. Am I way off? Any help or suggestions?

{=LARGE((Raw!B:B,$E$6,Raw!C:C,IF($E$3="All","*",$E$3),Raw!M:M,IF($E$4="All","*",$E$4),Raw!N:N,IF($E$5="All","*",$E$5),Raw!A:A,$G$2,Raw!P:P,$F$2,Raw!J:J),C10)}

The values I want to "Large" are in column J:J.

Thanks in advance

[mmmarks]

I think , your formula syntactically wrong ..look at bold area and see syntax of large formula.


{=LARGE((Raw!B:B,$E$6,Raw!C:C,IF($E$3="All","*",$E$3),Raw!M:M,IF($E$4="All","*",$E$4),Raw!N:N,IF($E$ 5="All","*",$E$5),Raw!A:A,$G$2,Raw!P:P,$F$2,Raw!J:J),C10)}

[notimepelican]

Hey mmmarks

Can you please elaborate a little more.

My array is RawJ:J but I only want it to large the values if the value meets the conditions of "Raw!B:B,$E$6,Raw!C:C,IF($E$3="All","*",$E$3),Raw!M:M,IF($E$4="All","*",$E$4),Raw!N:N,IF($E$ 5="All","*",$E$5),Raw!A:A,$G$2,Raw!P:P,$F$2". In the case of E3, E4 and E5 if the text in the cell equals "All" I need the large formula to find the value on my "Raw" sheet based on the remaining conditions - the value in C10 is one, C11 two etc.

I have used a similar working formula for Sumifs "SUMIFS(Raw!$H:$H,Raw!$A:$A,IF($C$5="All","*",$C$5),Raw!$C:$C,IF($C$4="All","*",$C$4),Raw!$B:$B,$B13,Raw!$L:$L,IF($C$6="All","*",$C$6),Raw!$N:$N,IF($C$8="All","*",$C$8),Raw!$M:$M,IF($C$7="All","*",$C$7),Raw!$O:$O,IF($C$9="All","*",$C$9))" and have adapted the large to match this.

As mentioned I have the Large formula working without the "All" conditions for those three cells.

Any further expansion on your response would be great

Regards

[notimepelican]

Bump as I need a solution.

So looking at my earlier attempt at a solution I see that I am way off. Therefore need to work it out adapting the current working formula that does not contain the "All" criteria.

{=IFERROR(LARGE(IF(Table1[Week]='Top Products'!$E$6,IF(Table1[Account]='Top Products'!$E$3,IF(Table1[Cat D]='Top Products'!$E$4,IF(Table1[MA]='Top Products'!$E$5,IF(Table1[Year]='Top Products'!$G$2,IF(Table1[LT Chain]='Top Products'!$F$2,Table1[Conversions])))))),C10),"---")} works, however for the E3, E4 and E5 I need to add an "All" criteria. In that if "All" is selected from my drop downs the formula will calculate the large function for all the values within that criteria based on the other criteria.

Any help please.