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

1. ## Re: Unique count based on different cells

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

2. ## Re: Unique count based on different cells Originally Posted by Sahitya 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?  Reply With Quote

3. ## Re: Unique count based on different cells Originally Posted by Aladin Akyurek 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.  Reply With Quote

4. ## Re: Unique count based on different cells Originally Posted by Sahitya 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?  Reply With Quote

5. ## Re: Unique count based on different cells Originally Posted by Aladin Akyurek 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.  Reply With Quote

6. ## Re: Unique count based on different cells Originally Posted by Sahitya 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))),"")  Reply With Quote

7. ## 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  Reply With Quote

8. ## Re: Unique count based on different cells

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  Reply With Quote

9. ## Re: Unique count based on different cells Originally Posted by Sahitya 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))),"")  Reply With Quote

## User Tag List

#### Tags for this Thread

#n/a, operations, services, software, unique count #### Posting Permissions

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