Count the min unique items excluding blanks

[Blunder1]

Hi All,

I need a formula (cant use filters ot pivot table for this spreadsheet) that will count the lowest number on unique items in columns B. The amont of rows in columns B will change daily and intra day but i dont want to keep adjusting the formula parameters ot including blank cells.

This is the formula i'm using is below. I need this to be generic to column B

=IF('Cash Manual Matching'!B1="","",MIN(COUNTIF('Cash Manual Matching'!B2:B1859,'Cash Manual Matching'!B2:B1859)))

Thanks

Blunder

 

[MrKowz]

What type of data is in column B (strings or numbers)? I'd reccomend creating a named range that is defined as either:

String Data:
='Cash Manual Matching'!B2:INDEX('Cash Manual Matching'!B:B,MATCH(REPT("Z",255),'Cash Manual Matching'!B:B))

Numerical Data:
='Cash Manual Matching'!B2:INDEX('Cash Manual Matching'!B:B,MATCH(9.99E+307,'Cash Manual Matching'!B:B))

 

[Blunder1]

Thank you this, but the formula give me the cell value, what i need is the amount.
Ie if the columns had:

121
121
222
222
222
222
322
322
322
322
322
322
The formula would then give a result of 2 ( being only two items in the smallest unique group)

Thanks

 

[T. Valko]

Hi All,

I need a formula (cant use filters ot pivot table for this spreadsheet) that will count the lowest number on unique items in columns B. The amont of rows in columns B will change daily and intra day but i dont want to keep adjusting the formula parameters ot including blank cells.

This is the formula i'm using is below. I need this to be generic to column B

=IF('Cash Manual Matching'!B1="","",MIN(COUNTIF('Cash Manual Matching'!B2:B1859,'Cash Manual Matching'!B2:B1859)))

Thanks

Blunder

Hmmm...

Is this what you mean...

<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=72 height=17>data1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>data3</TD></TR></TBODY></TABLE>

Data1 appears the fewest times so the result you want is 2?

 

[MrKowz]

Thank you this, but the formula give me the cell value, what i need is the amount.
Ie if the columns had:

121
121
222
222
222
222
322
322
322
322
322
322
The formula would then give a result of 2 ( being only two items in the smallest unique group)

Thanks

I meant to create a named range, not replace the formula.

Try this, go to Insert>Name>Define. Give it the name "ColB", and define the range to be:
='Cash Manual Matching'!B2:INDEX('Cash Manual Matching'!B:B,MATCH(9.99E+307,'Cash Manual Matching'!B:B)).

Then in your worksheet, change your formula to:
=IF('Cash Manual Matching'!B1="","",MIN(COUNTIF(ColB,ColB)))

 

[T. Valko]

Thank you this, but the formula give me the cell value, what i need is the amount.
Ie if the columns had:

121
121
222
222
222
222
322
322
322
322
322
322
The formula would then give a result of 2 ( being only two items in the smallest unique group)

Thanks

Try this array formula**:

=SMALL(FREQUENCY(A2:A13,A2:A13),SUM(IF(FREQUENCY(A2:A13,A2:A13)=0,1))+1)

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Blunder1]

ahhh, never seen that befor Mr Kowtz but it worked liek a charm. Thank you for you help

 

[T. Valko]

Hmmm...

Is this what you mean...

Apparently that's NOT what you meant.

Thanks for confirming that!