+ Reply to Thread
Results 1 to 7 of 7

Can you explain how this formula works?

  1. #1
    Registered User
    Join Date
    12-04-2019
    Location
    Vancouver, BC
    MS-Off Ver
    Office 365
    Posts
    4

    Unhappy Can you explain how this formula works?

    Hi all the experts,

    I had received a with the following array formula, and couldn't understand how it works:

    =IFERROR(INDEX($D$2:$D$91,MATCH(0,COUNTIF($F$1:F1,$D$2:$D$91),0)),"")

    Is anyone able to explain to me? we can ignore the IFERROR as I understand what that does

    I also understand how Index and Match work ...and had been using COUNTIF for counting numbers of cell that meets my criteria
    yet ....... I cannot understand how the COUNTIF work here and how it leads to the result in the F column

    I believe the purpose of the formula is to filter out all the duplicated items on column D but how? Please help.

    Capture.JPG

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,338

    Re: Can you explain how this formula works?

    Hi,

    The Countif sets up an array of 1s or 0s for where the values in all the cells above the current formula exist in the whole of column D. Where ther is a first occurrence of the column d cell there will always be a zero in the array and hence the MATCH with zero will return the row number in column D where that value is found an then Index uses that row number.

    Where there is a duplicate the countif array will contain all 1s, and hence a Match for the value 0 will return an error and hence a blank

    However since you have 365 a better formula would be

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which will then Spill the fomula automatically as far as required to list the unique values.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-04-2019
    Location
    Vancouver, BC
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Can you explain how this formula works?

    Thank you Richard for your prompt reply.

    I do not know why, but I do not have the UNIQUE formula
    I used to have XLOOKUP before I switched computer

    I think it is the same reason I am not getting UNIQUE; any idea?


    in addition, I do understand your explanation.
    I bet my struggle is I do not understand how we can getting the 1s and the 0s

    I thought the COUNTIF formula works as =COUNTIF (range, criteria)

    in the above formula, range we are trying to look at is $F$1:F1, which will increase to $F$1:F2 as we move downward
    However, the criteria is not "text" or number .... but $D$2:$D$91 ... how does it automatically return the value (which is text from the D column) ...
    or what my struggle is to understand, why it F3 result is "New Sale/Account" instead of "Package Changes*"? they both appeared only once between F1 and F3

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,026

    Re: Can you explain how this formula works?

    Stay in F3
    COUNTIF($F$1:F2,$D$2:$D$91)

    how to set up an array of 1s or 0s

    COUNTIF($F$1:F2,D2)=1
    COUNTIF($F$1:F2,D3)=0
    COUNTIF($F$1:F2,D4)=0
    ....
    COUNTIF($F$1:F2,D91)=0

    yield an array {1;0;0;0;...} where first "0" indicates 1st position (D3) that not exist in F1:F2
    then MATCH(...) returns 2, with this INDEX give "New sale/Account"

  5. #5
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Can you explain how this formula works?

    Quote Originally Posted by Richard Buttrey View Post
    . . . The Countif sets up an array of 1s or 0s . . .
    Picky: it's produces and array of 0s or positive values. Note that Technical Support appears twice in col D, so in the formula in col F in the row just below Technical Support in that column, COUNTIF would give 2 (or more) as the value in the 6th and 9th entries the array it returns. The 0s are important. The positive values are irrelevant other than for not being 0s.

  6. #6
    Registered User
    Join Date
    12-04-2019
    Location
    Vancouver, BC
    MS-Off Ver
    Office 365
    Posts
    4
    Quote Originally Posted by bebo021999 View Post
    Stay in F3
    COUNTIF($F$1:F2,$D$2:$D$91)

    how to set up an array of 1s or 0s

    COUNTIF($F$1:F2,D2)=1
    COUNTIF($F$1:F2,D3)=0
    COUNTIF($F$1:F2,D4)=0
    ....
    COUNTIF($F$1:F2,D91)=0

    yield an array {1;0;0;0;...} where first "0" indicates 1st position (D3) that not exist in F1:F2
    then MATCH(...) returns 2, with this INDEX give "New sale/Account"
    Oh mine ... You solved the mystery.
    Well my mystery .... I made it sound like something not everyone know but might just be me stupid question.


    Thank you both of you for explaining it to me.

  7. #7
    Registered User
    Join Date
    12-04-2019
    Location
    Vancouver, BC
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Can you explain how this formula works?

    Quote Originally Posted by mpun View Post
    Thank you Richard for your prompt reply.

    I do not know why, but I do not have the UNIQUE formula
    I used to have XLOOKUP before I switched computer

    I think it is the same reason I am not getting UNIQUE; any idea?


    in addition, I do understand your explanation.
    I bet my struggle is I do not understand how we can getting the 1s and the 0s

    I thought the COUNTIF formula works as =COUNTIF (range, criteria)

    in the above formula, range we are trying to look at is $F$1:F1, which will increase to $F$1:F2 as we move downward
    However, the criteria is not "text" or number .... but $D$2:$D$91 ... how does it automatically return the value (which is text from the D column) ...
    or what my struggle is to understand, why it F3 result is "New Sale/Account" instead of "Package Changes*"? they both appeared only once between F1 and F3
    Turns out my office 365 didn't update to newest version automatically like it should.

    Finally get my xlookup back so I think I can get the unique formula now

    THanks for suggesting that Richard

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Can somebody explain how this formula works?
    By ocannon1 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-12-2018, 11:25 AM
  2. [SOLVED] Can someone explain HOW this formula works?
    By jmrlifesafe in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-25-2016, 10:19 AM
  3. [SOLVED] explain my formula and HOW it works (please)
    By galvinpaddy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2015, 05:02 PM
  4. Explain How Specific R1C1 Formula Works
    By chouston in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2013, 02:32 PM
  5. Replies: 3
    Last Post: 11-08-2012, 09:00 AM
  6. [SOLVED] Can Someone Explain how this formula works?
    By Brightspark in forum Excel General
    Replies: 4
    Last Post: 06-08-2012, 03:40 PM
  7. Explain how vlookup works
    By rajesh.chandra in forum Excel General
    Replies: 2
    Last Post: 07-10-2009, 09:51 AM

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