Forum Discussion

DCBanks's avatar
DCBanks
Copper Contributor
Nov 17, 2019
Solved

INDEX, SMALL, COUNTIF

I am using INDEX, SMALL, COUNTIF to extract records from a list with 10 columns and averages 200 rows. There's is a 2 text analysis code and a 3 text budget code, examples are AD, EQ, ST for analysis...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 17, 2019

    DCBanks 

    If to extract by formulas records from list based on few criteria, I'd use AGGREGATE to avoid array formulas.

    In this case

    formula could be like

    =IFERROR(
   INDEX(A$5:A$38,
      AGGREGATE(
         15,
         6,
         1/(**bleep**=$O$10)/(Budget=$P$10)/(ProjectCode=$Q$10)*
            (ROW($A$5:$A$38)-ROW($A$4)),
         (ROW()-ROW($O$10))
      )
   ),
"")

    and in 1/()/().. part you may add as many criteria as needed.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 17, 2019

DCBanks 

If to extract by formulas records from list based on few criteria, I'd use AGGREGATE to avoid array formulas.

In this case

formula could be like

=IFERROR(
   INDEX(A$5:A$38,
      AGGREGATE(
         15,
         6,
         1/(**bleep**=$O$10)/(Budget=$P$10)/(ProjectCode=$Q$10)*
            (ROW($A$5:$A$38)-ROW($A$4)),
         (ROW()-ROW($O$10))
      )
   ),
"")

and in 1/()/().. part you may add as many criteria as needed.

DCBanks's avatar
DCBanks
Copper Contributor
Nov 18, 2019

SergeiBaklan 

Thank you, I've found a new best friend in AGGREGATE, just what I wanted.

If one of the criteria entered is blank can I force it to be true and list all codes in the range. We have one manager who needs to see all projects (PRO) by putting a blank in the project code (P5, P4 etc.).

As someone said in another forum - 'The best Excel function you're not using'

ps the bleep was because I used the word A N A L, short for Analysis!

Thanks again.

Resources