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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
not clear to me yet - show us all desired outputs - for operations there are 5 rows - with 5 suppliers - one site and 4 n/a's
 
Upvote 0
not clear to me yet - show us all desired outputs - for operations there are 5 rows - with 5 suppliers - one site and 4 n/a's

Is this fine now?


Operations
2019+20182017-Not known
Unique Suppliers2102
Pear
Orange
Kiwi Apple
Watermelon
Research
2019+20182017-Not known
Unique Suppliers0210
Kiwi
Fig
Banana

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
#N/A in the 'Due Date' to be mapped under Not Known.

it tried Sum -frequency. but it didn't work for me.
:(


Book1
ABCDEFGHIJ
1FunctionsSuppliersSitesDue DateOperations
2OperationsApple#N/A#N/A2019+20182017-Not known
3OperationsOrange#N/A4/13/2020Unique Suppliers2102
4End User ServicesWatermelon#N/A#N/A
5OperationsPineapple#N/A#N/AOrangeKiwiApple
6OperationsKiwiMulgrave4/13/2018PearPineapple
7ResearchFigMulgrave4/13/2018
8ResearchKiwiMulgrave4/13/2018
9ResearchKiwiCalifornia4/13/2018
10ResearchKiwiCalifornia4/13/2018
11ResearchBanana#N/A3/30/2012
12OperationsPearNA9/15/2019
13Software ServicesBerrySydney#N/A
14Software ServicesBerrySydney7/9/2018
15Software ServicesGrapeSydney7/9/2018
16Software ServicesBerryArizona#N/A
17Software ServicesBerryArizona12/12/2017
Sheet1


In G2 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In G5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")


In H3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In H5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

In I3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In I5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

In J3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In I5 control+shift+enter, not just enter, 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,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

The same set up also holds for other items like 'reserach'.
 
Upvote 0
ABCDEFGHIJ
1FunctionsSuppliersSitesDue DateOperations
2OperationsApple#N/A#N/A2019+20182017-Not known
3OperationsOrange#N/A4/13/2020Unique Suppliers2102
4End User ServicesWatermelon#N/A#N/A
5OperationsPineapple#N/A#N/AOrangeKiwiApple
6OperationsKiwiMulgrave4/13/2018PearPineapple
7ResearchFigMulgrave4/13/2018
8ResearchKiwiMulgrave4/13/2018
9ResearchKiwiCalifornia4/13/2018
10ResearchKiwiCalifornia4/13/2018
11ResearchBanana#N/A3/30/2012
12OperationsPearNA9/15/2019
13Software ServicesBerrySydney#N/A
14Software ServicesBerrySydney7/9/2018
15Software ServicesGrapeSydney7/9/2018
16Software ServicesBerryArizona#N/A
17Software ServicesBerryArizona12/12/2017

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1


In G2 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In G5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")


In H3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In H5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

In I3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In I5 control+shift+enter, not just enter, 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,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

In J3 control+shift+enter, not just enter:

=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,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

In I5 control+shift+enter, not just enter, 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,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

The same set up also holds for other items like 'reserach'.

Thanks for the code Aladin. But Sites (Column C) was not considered. If same supplier has two different Sites, then it should be calculated as 2 unique suppliers not as 1.
 
Upvote 0
Thanks for the code Aladin. But Sites (Column C) was not considered. If same supplier has two different Sites, then it should be calculated as 2 unique suppliers not as 1.

Please do not quote the whole reply.

The formulas do deliver the output you specified.


And if sites must be taken into account, what must happen when a site is indicated as #N/A?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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