ARRAY formula

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. Windows
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"),

thanks in advance :)

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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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"),

thanks in advance :)

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)),"")

 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top