Unique count based on different cells
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Unique count based on different cells
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    X,#N/A - it is considered as two sites. Even x, y is 2 sites

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    X,#N/A - it is considered as two sites. Even x, y is 2 sites
    operations,apple,#N/A,1/7/2018
    operations,apple,london,1/12/2018

    What do we have here: 1 or 2 unique records?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #13
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Aladin Akyurek View Post
    operations,apple,#N/A,1/7/2018
    operations,apple,london,1/12/2018

    What do we have here: 1 or 2 unique records?
    Yes Aladin sir. You are absolutely correct.

    Read blog about you. Hats off you to you sir.

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    Yes Aladin sir. You are absolutely correct.

    Read blog about you. Hats off you to you sir.
    Thnaks for the kind words. Just to be sure: Do the formulas I proposed satisfy your goal?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #15
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Aladin Akyurek View Post
    operations,apple,#N/A,1/7/2018
    operations,apple,london,1/12/2018

    What do we have here: 1 or 2 unique records?
    we will have 2 unique records.

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    we will have 2 unique records.
    G3: Control+shift+enter (CSE)...

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

    G5: CSE and copy down...

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    H3: CSE...

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

    H5: CSE and copy down...

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    I3: CSE...

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))


    I5: CSE and copy down...

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    J3: CSE...

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

    J5: CSE and copy down...

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #17
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Sir, your formula is working perfectly alright but i have small query, currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B

  8. #18
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    @Aladin Akyurek
    Sir, i have two issues now
    1)

    currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B
    2) If
    operations,apple,#N/A,1/7/2018
    operations,apple,london,1/12/2018 - it considered as two
    But if data is like
    operations,apple,#N/A,1/7/2018
    operations,apple,Sweden,1/12/2018
    operations,apple,london,1/12/2018 - it needs to be considered as 3 but still considered as two

  9. #19
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    Sir, your formula is working perfectly alright but i have small query, currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B
    01. What follows assumes that the data is located in Sheet1. Adjust the sheet name if necessary.

    02. Define Lrow in the Name Manager as referring to...

    =MATCH(REPT("z",255),Sheet1!$A:$A)

    03. Define Functions as...

    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

    04. Define Suppliers as...

    =Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

    05. Define Sites as...

    =Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

    06. Define DueDates as...

    =Sheet1!$D$2:INDEX(Sheet1!$D:$D,Lrow)

    07. Define Ivec as...

    =ROW(Functions)-ROW(INDEX(Functions,1,1))+1

    The foregoing enables to install a dynamic set up, i.e. a set up which adjusts itself to record addition or deletion.

    The formulas can now be rewritten in terms of the dynamic named ranges specified in 1 to 7.

    08. G3, control+shift+enter…

    =SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

    09. G5, control+shift+enter and copy down...

    =IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

    10. H3, control+shift+enter…

    =SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

    11. H5, control+shift+enter and copy down...

    =IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

    12. I3, control+shift+enter…

    =SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

    13. I5, control+shift+enter and copy down...

    =IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

    14. J3, control+shift+enter…

    =SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),1))

    15. J5, control+shift+enter and copy down...

    =IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),Ivec),ROWS($1:1))),"")
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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