Unique count based on different cells

Sahitya

New Member
Joined
May 29, 2018
Messages
27
I need to get unique value of Suppliers based on the different columns

Input:
FunctionsSuppliersSitesDue Date
OperationsApple#N/A#N/A
OperationsOrange#N/A4/13/2020
End User ServicesWatermelon#N/A#N/A
OperationsPineapple#N/A#N/A
OperationsKiwiMulgrave4/13/2018
ResearchFigMulgrave4/13/2018
ResearchKiwiMulgrave4/13/2018
ResearchKiwiCalifornia4/13/2018
ResearchKiwiCalifornia4/13/2018
ResearchBanana#N/A3/30/2012
OperationsPearNA9/15/2019
Software ServicesBerrySydney#N/A
Software ServicesBerrySydney7/9/2018
Software ServicesGrapeSydney7/9/2018
Software ServicesBerryArizona#N/A
Software ServicesBerryArizona12/12/2017

<tbody>
</tbody>


Output:

2019+20182017-Not known
Unique Suppliers

<tbody>
</tbody>


Condition for output :
A = Function (Specific), B = Unqiue, C = different, D = date

For Example : Suppliers unique count when 'Function' is 'Operations' , 'Sites' are different and 'Due Date' as per the column header (2019+, 2018, 2017-, #N/A to be mapped under Not Known.
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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))),"")
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
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))),"")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top