Large if or

[joeltes]

Hi, trying LARGE IF for the first time (array formula) but I cant get it to work.. How can I correct the formula or is there a better formula I should be using

{=LARGE(IF((OR('30 DAYS'!A:A="1",'30 DAYS'!A:A="IL")),IF('30 DAYS'!D:D=NJIL!A:A))),1)}

 

[mikerickson]

I'm not sure what you are trying to do, but other than the 1 (signifying largest), all of the elements are boolean, which Large ignores.

 

[joeltes]

I realize the formula above does not make any sense. I'm trying to get the largest number in a column, where the largest number matches three criteria
This is the next formula I tried =SUMPRODUCT(LARGE(('30 DAYS'!A:A="1")*('30 DAYS'!A:A="IL")*('30 DAYS'!D:D=A:A)*('30 DAYS'!F:F),1))

 

[Ron Coderre]

Try this regular (NON-array) formula...

=LARGE(INDEX((('30 Days'!A1:A20="1")+('30 Days'!A1:A20="IL"))*('30 Days'!D1:D20='30 Days'!A1:A20)*('30 Days'!F1:F20),0),1)

Is that something you can work with?

 

[joeltes]

Didn't work either. I should have done this right from the start.. Please see below, RESULTS SHEET has the formula looking at 30 Days.



RESULTS SHEET (SHEET1)
[TABLE="class: grid, width: 212"]
<tbody>[TR]
[TD][/TD]
[TD]LARGEST NUMBER OF SALES[/TD]
[/TR]
[TR]
[TD]ITEM1[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]ITEM2[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]ITEM3[/TD]
[TD="align: right"]39[/TD]
[/TR]
</tbody>[/TABLE]


30 Days (SHEET2)
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]BRANCH[/TD]
[TD="width: 64"]ITEM[/TD]
[TD="width: 64"]NUMBER OF SALES[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM1[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM2[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]ITEM1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]ITEM3[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM3[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]ITEM3[/TD]
[TD]48[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

The example was very helpful...
Try this regular formula on the results tab, copied down:

B2: =LARGE(INDEX(((Data!$A$2:$A$14=1)+(Data!$A$2:$A$14="IL"))*(Data!$B$2:$B$14=Sheet4!A2)*Data!$C$2:$C$14,0),1)

Does that help?

Note: I'd strongly recommend setting up your data in Excel Tables

 

[joeltes]

It didn't work, but I see I need to study this a little more before asking for help on the board. Thanks y'all for your time