Large if or

joeltes

New Member
Joined
Mar 16, 2018
Messages
21
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)}
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,789
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

New Member
Joined
Mar 16, 2018
Messages
21
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Try this regular (NON-array) formula...
Code:
=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

New Member
Joined
Mar 16, 2018
Messages
21

ADVERTISEMENT

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
ITEM126
ITEM221
ITEM339

<tbody>
</tbody>


30 Days (SHEET2)
BRANCHITEMNUMBER OF SALES
1ITEM126
1ITEM221
ILITEM112
ILITEM339
1ITEM316
TXITEM348

<tbody>
</tbody>
 
Last edited:

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
The example was very helpful...
Try this regular formula on the results tab, copied down:
Code:
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

New Member
Joined
Mar 16, 2018
Messages
21
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,404
Members
409,871
Latest member
i1patrick
Top