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)

	LARGEST NUMBER OF SALES
ITEM1	26
ITEM2	21
ITEM3	39

<tbody>
</tbody>

30 Days (SHEET2)

BRANCH	ITEM	NUMBER OF SALES
1	ITEM1	26
1	ITEM2	21
IL	ITEM1	12
IL	ITEM3	39
1	ITEM3	16
TX	ITEM3	48

<tbody>
</tbody>

 

[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