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.

DCBanks's avatar
DCBanks
Copper Contributor
Nov 17, 2019

Riny_van_Eekelen 

Re question 'what am I trying to achieve'. There are a number of projects of which each user is assigned a 'manager'. When they see how much their projects are costing they want to identify where and what they have spent the money on rather than trawling through hundreds of rows, 90% which they are not interested in. They want to input their project(s) and the type of purchase and get a list. I am going to add a column to the 'report' for each line as a percentage of the total budget so that they can immediately see where all the money has gone.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 17, 2019

DCBanks 

Have quite some experience with users who don't know much about Excel. Prefer to teach them some basics on how to use a fairly simple sheet, rather than designing a sheet that becomes complex and that would have to be fool-proof so that users can't screw it up. But that's your choice.

Resources