# ARRAY formula

#### orsm6

##### Active Member
Hi all, on a spreadsheet I would like to list the top ten values of a list of items using the LARGE function and sets of criteria, I entered this formula in and it works, but when I try to expand it by adding another 3 or more criteria it doesn't work, any suggestions please on how I can add extra criteria to make it work??

{=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000"),'sap dump 2'!G:G),ROW(A1)),"")}

I want to add more criteria that may look like this: ('sap dump 2'!C:C>="20000000")*('sap dump 2'!C:C<"30000000"),

EDIT: When I say it works I mean that it will list the top ten with that criteria, but because I cant expand the criteria I am missing data

Last edited:

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### jarjarbingie

##### Well-known Member
Hi all, on a spreadsheet I would like to list the top ten values of a list of items using the LARGE function and sets of criteria, I entered this formula in and it works, but when I try to expand it by adding another 3 or more criteria it doesn't work, any suggestions please on how I can add extra criteria to make it work??

{=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000"),'sap dump 2'!G:G),ROW(A1)),"")}

I want to add more criteria that may look like this: ('sap dump 2'!C:C>="20000000")*('sap dump 2'!C:C<"30000000"),

EDIT: When I say it works I mean that it will list the top ten with that criteria, but because I cant expand the criteria I am missing data
=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000")*('sap dump 2'!C:C>=20000000)*('sap dump 2'!C:C<30000000),'sap dump 2'!G:G),ROW(A1)),"")

#### orsm6

##### Active Member
Hi jarjar... thank you for the reply. I tried this formula but it returns a blank cell. (tried as an array and as a normal formula)

thanks.

#### FDibbins

##### Well-known Member
1st, try and avoid using full-column references in an array formula, it could slow your file down

2nd, testing for multiple criteria to the same column can be tricky but not impossible), but looking at your 1st and 2nd formulas, something does not look right.
you are testing for (sap dump 2'!C:C<"PA100000") which implies that C contains text?
But then you are looking to test for within a value range in the same column? 'sap dump 2'!C:C<"PA100000")

So does C contain text or value?

I would probably do this with a helper column to ID which rows to consider

#### orsm6

##### Active Member
Hi FDibbins.... Yes column C has text and it has numbers.

I think I know what you mean by helper column, i'll give that a go. Appreciate your response, thanks.

#### FDibbins

##### Well-known Member
Hi FDibbins.... Yes column C has text and it has numbers.

I think I know what you mean by helper column, i'll give that a go. Appreciate your response, thanks.
OK then excel will have a hard time with that test, it is either testing for a text string OR a value range, it wont be able to do both, I think a helper column will serve you better.

=if(or(C1="string",c1>min,C1<max),G1,0)

#### orsm6

##### Active Member
not following your formula, what is the 'string' and what part of the formula returns the value I am looking for?

in my helper column (formula cell F2) look at cell C2 and if these criteria met return PAC, or RAW ...

criteria:

greater than less than
PA00000 PA10000
RA00000 RA70000
10000000 20000000

<ra70000,><pa10000
<pa100000 <pa100000
<ra10000
<ra10000 <ra700000
20000000 300000000

if column D contains the word "REWORK" then cell F2 would return REW</ra10000></ra10000
</pa100000></pa10000
</ra70000,></pa10000,>

Last edited:

#### FDibbins

##### Well-known Member
OK, just to make sure I understand you here.

Are you saying you have "values" in the range of PA00000 to PA10000?

If so, those are not values, they are text, and trying to find if something falls within that "range", is lake asking excel if cat falls within tree and fish.

#### orsm6

##### Active Member
Column C is formatted as "general"... not by number or text

Column C contains lots of different values, they could be anywhere between
PA00000 and PA10000
RA00000 and RA70000
10000000 and 20000000
20000000 and 30000000

so yes, there is text and there is numbers, but all of the column is formatted as General....if that helps. I can do a SUMIFS formula which works, so not sure why I can't do what I need as above in the other post... =SUM(SUMIFS(G:G,C:C,">=20000000",C:C,"<30000000")+SUMIFS(G:G,C:C,">=PA000000",C:C,"<=PA100000"))/1000000

#### FDibbins

##### Well-known Member
doesn't matter how it is formatted, if you have any text in a cell, even if there are numbers as well, that cell is text. If you have PA00000 in a cell, that is text