SOLVED
Home

INDEX, SMALL, COUNTIF

%3CLINGO-SUB%20id%3D%22lingo-sub-1013717%22%20slang%3D%22en-US%22%3EINDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013717%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20INDEX%2C%20SMALL%2C%20COUNTIF%20to%20extract%20records%20from%20a%20list%20with%2010%20columns%20and%20averages%20200%20rows.%20There's%20is%20a%202%20text%20analysis%20code%20and%20a%203%20text%20budget%20code%2C%20examples%20are%20AD%2C%20EQ%2C%20ST%20for%20analysis%20code%20and%20MAR%2C%20RUN%20for%20project%20code.%20The%20current%20code%20is%3A%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(AllData%2CSMALL(IF(COUNTIF(%24Q%2440%2C**bleep**)*COUNTIF(%24Q%2441%2CProjectCode)%2CROW(TSB!%24A%245%3A%24T%24119)-MIN(ROW(TSB!%24A%245%3A%24T%24119))%2B1)%2CROW(A1))%2CCOLUMN(A1))%2C%22%22)%20(plus%20curly%20brackets.)%3C%2FP%3E%3CP%3EI%20want%20to%20search%20on%20more%20than%20one%20analysis%20code%20(Q40)%20so%20I%20thought%20I%20just%20needed%20to%20add%20another%20COUNTIF%20but%20realised%20as%20I%20was%20doing%20it%20that%20I%20was%20trying%20to%20use%20a%203%20dimensional%20array%2C%20of%20course%20it%20didn't%20work.%26nbsp%3B%20So%20I%20tried%20it%20on%20the%20Project%20Code%20with%20a%20COUNTIFS%2C%20see%20next%2C%20it%20worked%20over%203%20inputs%3A%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(AllData%2CSMALL(IF(COUNTIF(%24G%2410%2C**bleep**)*COUNTIFS(%24H%2410%2CBudget%2C%24I%2410%2CProjectCode)%2CROW(%24B%245%3A%24F%2438)-MIN(ROW(%24B%245%3A%24F%2438))%2B1)%2CROW(A1))%2CCOLUMN(A1))%2C%22%22)%3C%2FP%3E%3CP%3EAha%2C%20I%20thought%2C%20so%20I%20added%20a%20second%20criteria%20to%20the%20first%20COUNTIFS%20but%20that%20didn't%20work.%20Is%20it%20because%20I%20am%20trying%20to%20do%20a%20COUNTIFS%20twice%20on%20the%20same%20range%3F%20There%20could%20be%20up%20to%205%20analysis%20codes%20to%20one%20project.%3C%2FP%3E%3CP%3EIdeally%20I%20would%20like%20to%20put%20up%20to%205%20criteria%20in%20the%20first%20COUNTIFS%20and%20wildcards%20into%20the%20second.%20I%20really%20do%20get%20my%20nickers%20in%20a%20twist%20over%20%26amp%3B%2C%20%22%20and%20'.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20attached%2C%20thanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1013717%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013742%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460090%22%20target%3D%22_blank%22%3E%40DCBanks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20an%20amazingly%20complicated%20formula.%20What%20exactly%20are%20you%20trying%20to%20achieve%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20considered%20putting%20a%20filter%20on%20your%20column%20headers%3F%26nbsp%3BIt%20seems%20you%20can%20get%20the%20same%20extract%20by%20just%20ticking%20the%20relevant%20check%20boxes%20in%20the%20column%20filters.%20E.g.%2C%20you%20can%20select%20three%20Analysis%20codes%2C%20a%20Budget%20code%20and%20a%20Project%20code%20and%20then%20it%20will%20display%20all%20rows%20that%20match%20that%20filter.%20That's%20roughly%20what%20your%20formula%20is%20doing%20now%20for%20only%20one%20Analysis%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013801%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20I%20can%20do%20that%20and%20have%20tried%20it%20out%20on%20the%20users%20who%20have%20knowledge%20of%20Excel%20ranging%20from%20nothing%20to%20nought!%20The%20users%20numbering%2040%20plus%20ring%20me%20up%20and%20tell%20me%20they%20are%20missing%20transactions%20and%20I%20ask%20them%20they%20can%20see%20little%20arrows%20at%20the%20top%20of%20the%20columns%20and%20they%20ask%20me%20'what%20is%20a%20column'!%20Entering%203%20cells%20is%20about%20all%20they%20can%20manage.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013807%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%20question%20'what%20am%20I%20trying%20to%20achieve'.%20There%20are%20a%20number%20of%20projects%20of%20which%20each%20user%20is%20assigned%20a%20'manager'.%20When%20they%20see%20how%20much%20their%20projects%20are%20costing%20they%20want%20to%20identify%20where%20and%20what%20they%20have%20spent%20the%20money%20on%20rather%20than%20trawling%20through%20hundreds%20of%20rows%2C%2090%25%20which%20they%20are%20not%20interested%20in.%20They%20want%20to%20input%20their%20project(s)%20and%20the%20type%20of%20purchase%20and%20get%20a%20list.%20I%20am%20going%20to%20add%20a%20column%20to%20the%20'report'%20for%20each%20line%20as%20a%20percentage%20of%20the%20total%20budget%20so%20that%20they%20can%20immediately%20see%20where%20all%20the%20money%20has%20gone.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013830%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460090%22%20target%3D%22_blank%22%3E%40DCBanks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20quite%20some%20experience%20with%20users%20who%20don't%20know%20much%20about%20Excel.%20Prefer%20to%20teach%20them%20some%20basics%20on%20how%20to%20use%20a%20fairly%20simple%20sheet%2C%20rather%20than%20designing%20a%20sheet%20that%20becomes%20complex%20and%20that%20would%20have%20to%20be%20fool-proof%20so%20that%20users%20can't%20screw%20it%20up.%20But%20that's%20your%20choice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013877%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460090%22%20target%3D%22_blank%22%3E%40DCBanks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20to%20extract%20by%20formulas%20records%20from%20list%20based%20on%20few%20criteria%2C%20I'd%20use%20AGGREGATE%20to%20avoid%20array%20formulas.%3C%2FP%3E%0A%3CP%3EIn%20this%20case%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20437px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157580i40ECC1FC53E02094%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(A%245%3AA%2438%2C%0A%20%20%20%20%20%20AGGREGATE(%0A%20%20%20%20%20%20%20%20%2015%2C%0A%20%20%20%20%20%20%20%20%206%2C%0A%20%20%20%20%20%20%20%20%201%2F(**bleep**%3D%24O%2410)%2F(Budget%3D%24P%2410)%2F(ProjectCode%3D%24Q%2410)*%0A%20%20%20%20%20%20%20%20%20%20%20%20(ROW(%24A%245%3A%24A%2438)-ROW(%24A%244))%2C%0A%20%20%20%20%20%20%20%20%20(ROW()-ROW(%24O%2410))%0A%20%20%20%20%20%20)%0A%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20in%201%2F()%2F()..%20part%20you%20may%20add%20as%20many%20criteria%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015978%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%2C%20SMALL%2C%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20I've%20found%20a%20new%20best%20friend%20in%20AGGREGATE%2C%20just%20what%20I%20wanted.%3C%2FP%3E%3CP%3EIf%20one%20of%20the%20criteria%20entered%20is%20blank%20can%20I%20force%20it%20to%20be%20true%20and%20list%20all%20codes%20in%20the%20range.%20We%20have%20one%20manager%20who%20needs%20to%20see%20all%20projects%20(PRO)%20by%20putting%20a%20blank%20in%20the%20project%20code%20(P5%2C%20P4%20etc.).%3C%2FP%3E%3CP%3EAs%20someone%20said%20in%20another%20forum%20-%20'The%20best%20Excel%20function%20you're%20not%20using'%3C%2FP%3E%3CP%3Eps%20the%20bleep%20was%20because%20I%20used%20the%20word%20A%20N%20A%20L%2C%20short%20for%20Analysis!%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
DCBanks
Occasional Contributor

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 code and MAR, RUN for project code. The current code is:

=IFERROR(INDEX(AllData,SMALL(IF(COUNTIF($Q$40,**bleep**)*COUNTIF($Q$41,ProjectCode),ROW(TSB!$A$5:$T$119)-MIN(ROW(TSB!$A$5:$T$119))+1),ROW(A1)),COLUMN(A1)),"") (plus curly brackets.)

I want to search on more than one analysis code (Q40) so I thought I just needed to add another COUNTIF but realised as I was doing it that I was trying to use a 3 dimensional array, of course it didn't work.  So I tried it on the Project Code with a COUNTIFS, see next, it worked over 3 inputs:

=IFERROR(INDEX(AllData,SMALL(IF(COUNTIF($G$10,**bleep**)*COUNTIFS($H$10,Budget,$I$10,ProjectCode),ROW($B$5:$F$38)-MIN(ROW($B$5:$F$38))+1),ROW(A1)),COLUMN(A1)),"")

Aha, I thought, so I added a second criteria to the first COUNTIFS but that didn't work. Is it because I am trying to do a COUNTIFS twice on the same range? There could be up to 5 analysis codes to one project.

Ideally I would like to put up to 5 criteria in the first COUNTIFS and wildcards into the second. I really do get my nickers in a twist over &, " and '. 

Example attached, thanks for any help.

6 Replies

@DCBanks 

It's an amazingly complicated formula. What exactly are you trying to achieve?

 

Have you considered putting a filter on your column headers? It seems you can get the same extract by just ticking the relevant check boxes in the column filters. E.g., you can select three Analysis codes, a Budget code and a Project code and then it will display all rows that match that filter. That's roughly what your formula is doing now for only one Analysis code.

@Riny_van_Eekelen 

I agree I can do that and have tried it out on the users who have knowledge of Excel ranging from nothing to nought! The users numbering 40 plus ring me up and tell me they are missing transactions and I ask them they can see little arrows at the top of the columns and they ask me 'what is a column'! Entering 3 cells is about all they can manage.

@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.

@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.

Solution

@DCBanks 

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

In this case

image.png

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.

@Sergei Baklan 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies