Unique count based on different cells

S

Sahitya

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

Input:
[TABLE="class: grid, width: 807"]
<tbody>[TR]
[TD]Functions[/TD]
[TD]Suppliers[/TD]
[TD]Sites[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Apple[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Orange[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]4/13/2020[/TD]
[/TR]
[TR]
[TD]End User Services[/TD]
[TD]Watermelon[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Pineapple[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Kiwi[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Fig[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]California[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]California[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Banana[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]3/30/2012[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Pear[/TD]
[TD]NA[/TD]
[TD="align: right"]9/15/2019[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Sydney[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Sydney[/TD]
[TD="align: right"]7/9/2018[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Grape[/TD]
[TD]Sydney[/TD]
[TD="align: right"]7/9/2018[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Arizona[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Arizona[/TD]
[TD="align: right"]12/12/2017[/TD]
[/TR]
</tbody>[/TABLE]


Output:

[TABLE="class: grid, width: 437"]
<tbody>[TR]
[TD][/TD]
[TD]2019+[/TD]
[TD]2018[/TD]
[TD]2017-[/TD]
[TD]Not known[/TD]
[/TR]
[TR]
[TD]Unique Suppliers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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
oldbrewer said:
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
Click to expand...

Is this fine now?


[TABLE="width: 437"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Operations[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2019+[/TD]
[TD]2018[/TD]
[TD]2017-[/TD]
[TD]Not known[/TD]
[/TR]
[TR]
[TD]Unique Suppliers[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Pear
Orange[/TD]
[TD]Kiwi[/TD]
[TD] [/TD]
[TD]Apple
Watermelon[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2019+[/TD]
[TD]2018[/TD]
[TD]2017-[/TD]
[TD]Not known[/TD]
[/TR]
[TR]
[TD]Unique Suppliers[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Kiwi
Fig[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sahitya said:
#N/A in the 'Due Date' to be mapped under Not Known.

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


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
Aladin Akyurek said:
ABCDEFGHIJ
FunctionsSuppliersSitesDue DateOperations
OperationsAppleNot known
OperationsOrangeUnique Suppliers
End User ServicesWatermelon
OperationsPineappleOrangeKiwiApple
OperationsKiwiMulgravePearPineapple
ResearchFigMulgrave
ResearchKiwiMulgrave
ResearchKiwiCalifornia
ResearchKiwiCalifornia
ResearchBanana
OperationsPearNA
Software ServicesBerrySydney
Software ServicesBerrySydney
Software ServicesGrapeSydney
Software ServicesBerryArizona
Software ServicesBerryArizona

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2019+[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2017-[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: right"]4/13/2020[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: right"]3/30/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]7/9/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]7/9/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]12/12/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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'.
Click to expand...

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
Sahitya said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
C
  • Question Question
Mutiple criteria filtering to separate 2NF table (nested FILTER over FILTER formula?)
Replies
1
Views
504
Fluff
Fluff
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
S
  • Solved
extract unique entries based on two criterias
2
Replies
14
Views
1K
snjpverma
S
K
  • Solved
Suggestions on how to re-write this formula for faster calculations
2
Replies
17
Views
2K
keef2
K

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

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