Results 1 to 9 of 9

INDEX-MATCH-SMALL-COUNTIFS returning duplicated results

Threaded View

  1. #1
    Registered User
    Join Date
    08-13-2021
    Location
    Toulouse, FR
    MS-Off Ver
    2013
    Posts
    4

    Question INDEX-MATCH-SMALL-COUNTIFS returning duplicated results

    Hello,

    I created a formula based on many post's helping users to get the top X rows of a table depending on a value in a specific column af the row.

    I have an Epics table containing an Epic element in each line. One of the columns correspond to the Cost Deviation, another one to the FixVersion. I want to get the Top 5 Epics Keys by Cost Deviation for a given FixVersion and display them in another 5 cells.

    The array formula I've created is as follows :

    In french:
    {=SIERREUR(INDEX(JEpicsTable[[Key]:[Key]];EQUIV(PETITE.VALEUR(NB.SI.ENS(JEpicsTable[[Cost Deviation]:[Cost Deviation]];">"&JEpicsTable[[Cost Deviation]:[Cost Deviation]];JEpicsTable[[FixVersions]:[FixVersions]];"*"&C$4&"*");LIGNE(1:1));NB.SI.ENS(JEpicsTable[[Cost Deviation]:[Cost Deviation]];">"&JEpicsTable[[Cost Deviation]:[Cost Deviation]];JEpicsTable[[FixVersions]:[FixVersions]];"*"&C$4&"*");0));"")}

    In french simplified:
    {=SIERREUR(INDEX(JEpicsTable[Key];EQUIV(PETITE.VALEUR(NB.SI.ENS(JEpicsTable[Cost Deviation];">"&JEpicsTable[Cost Deviation];JEpicsTable[FixVersions];"*"&C$4&"*");LIGNE(1:1));NB.SI.ENS(JEpicsTable[Cost Deviation];">"&JEpicsTable[Cost Deviation];JEpicsTable[FixVersions];"*"&C$4&"*");0));"")}

    In english simplified:
    {=IFERROR(INDEX(JEpicsTable[Key],MATCH(SMALL(COUNTIFS(JEpicsTable[Cost Deviation],">"&JEpicsTable[Cost Deviation],JEpicsTable[FixVersions],"*"&C$4&"*"),ROW(1:1)),COUNTIFS(JEpicsTable[Cost Deviation],">"&JEpicsTable[Cost Deviation],JEpicsTable[FixVersions],"*"&C$4&"*"),0)),"")}

    The value of the given FixVersion is in C$4.

    I am supposed to obtain the Top 5 elements by incrementing the ROW(1:1) like this : ROW(1:1), ROW(2:2), ROW(3:3)... but actually I obtain some duplicated keys in my results like A, A, A, B, B, C, D, E, F, F, G...
    All correct keys are there and in the correct order, but since there are some duplicated lines in the results, I can't obtain dynamically the Top 5 by just incrementing the row index from 1 to 5.

    Is there any way to filter duplicates (Duplicated keys in the results not duplicated Cost Deviations in the considered data) ? or to prevent genereting them by adapting the formula ?
    I'm new into array formulas and I'm not sure I completely understanding what Excel is internally doing whith the CountIfs. Evaluate formula didn't help me to get it.

    Thanks in advance for your help,
    Xavier

    Update 2021/08/30: Excel example has been attached to the post. It shows how repeated results are shown in the list, as well as some bigger values than others that are ignored. Thanks in advance for your help.
    Attached Files Attached Files
    Last edited by xavigg; 08-30-2021 at 10:42 AM. Reason: Adding Example

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to exclude a value from INDEX MATCH SMALL IF results based on criteria ?
    By TheGodfather in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2020, 03:48 PM
  2. Replies: 3
    Last Post: 01-31-2019, 08:00 PM
  3. Using INDEX, MATCH, and COUNTIFS to dissect survey results
    By gramdaman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2017, 04:12 PM
  4. [SOLVED] Index Small If Row Column formula not returning all results
    By EmmatheDancer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2015, 06:34 AM
  5. [SOLVED] Index Small If array formula with mulitple criteria returning incorrect results
    By EmmatheDancer in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-08-2015, 12:52 PM
  6. [SOLVED] Using Index match and small to come up with different results from same lookup
    By sirbletchley in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-19-2015, 07:37 PM
  7. [SOLVED] index match not returning all results
    By nellyc in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2013, 11:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1