# 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

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?

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

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.

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

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

Thnaks for the kind words. Just to be sure: Do the formulas I proposed satisfy your goal?

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

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. ## 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))),"")

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

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

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))),"")