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:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
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
 

Sahitya

New Member
Joined
May 29, 2018
Messages
27
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>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,176
#N/A in the 'Due Date' to be mapped under Not Known.

it tried Sum -frequency. but it didn't work for me.
:(
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Functions</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Suppliers</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sites</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Due Date</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Operations</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Operations</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Apple</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2019+</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2017-</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Not known</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Operations</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Orange</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Unique Suppliers</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">End User Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Watermelon</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Operations</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pineapple</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Orange</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Kiwi</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Apple</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Operations</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kiwi</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mulgrave</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Pear</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Pineapple</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Research</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fig</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mulgrave</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Research</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kiwi</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mulgrave</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Research</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kiwi</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">California</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Research</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kiwi</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">California</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/13/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Research</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Banana</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/30/2012</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Operations</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pear</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/15/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Software Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Berry</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sydney</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Software Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Berry</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sydney</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/9/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Software Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grape</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sydney</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7/9/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Software Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Berry</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Arizona</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Software Services</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Berry</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Arizona</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/12/2017</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br />

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'.
 

Sahitya

New Member
Joined
May 29, 2018
Messages
27
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,176
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,018
Messages
5,466,066
Members
406,462
Latest member
I10V3xl

This Week's Hot Topics

Top