Count Uniques In Visible Cells

Re: Count Uniques In Visible Cells

It can be done with formula, for your number of batches use =SUBTOTAL(3,B2:B100) and for your Subtotal use =SUBTOTAL(9,B2:B100)

Re: Count Uniques In Visible Cells

For a unique count of batch numbers in a filtered list, try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Re: Count Uniques In Visible Cells

First define the following...

Insert > Name > Define

Name: Array

Refers to:

=FREQUENCY(IF(SUBTOTAL(3,OFFSET($A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2),0,1)),MATCH("~"&$A$2:$A$100,$A$2:$A$100&"",0)),ROW($A$2:$A$100)-ROW($A$2)+1)

Click Ok

Then try...

=SUMPRODUCT(--(Array>0))

Hope this helps!

Re: Count Uniques In Visible Cells

See attached PivotTable.

Re: Count Uniques In Visible Cells

Nothing special really. Just made use of the Page Field to show/count only chosen batch number(s) by each subtotal number. Then used a basic COUNT function to count each subtotal number.

Re: Count Uniques In Visible Cells

For our example, let's assume we're using the following formula...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

For the OFFSET part part of the formula, you'll notice that the following array is used for the second argument...

ROW(A2:A10)-ROW(A2)

...which returns the following values...

0
1
2
3
4
5
6
7
8

As a result, OFFSET returns an array of ranges. Basically, it returns a single cell range for each cell in the original range. This array is passed to the SUBTOTAL function. Since 3 is the first argument of SUBTOTAL, it counts the number of items in each separate range. Since there is only one item in each range, SUBTOTAL returns 1 for each cell that is visible and 0 if the cell is not visible.

The MATCH part of the formula returns the position of each cell in A2:A10 within the range A2:A10. The tilde '~' is prepended to each lookup value so that special characters such as '*" and '?' are recognized as regular characters. Since doing so converts each value into a text value, a null string ("") is appended to each value in the lookup range to convert those to text values as well.

Then, the array of values returned by IF(SUBTOTAL(.....),MATCH(.....) is used by FREQUENCY for its values, and the array of values returned by ROW(...)-ROW(...)+1 is used by FREQUENCY for its bins. Have a look at the help file for a complete understanding of this function.

So let's say that A1:B10 contains the following data...

ColA ColA
6 x
6 x
2 x
8 y
6 x
8 y
4 x
4 y
7 y

...and that the data is filtered for 'x'...

ColA ColA
6 x
6 x
2 x
6 x
4 x

...here's how the formula is evaluated...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

=SUM(IF(FREQUENCY(IF({1;1;1;0;1;0;1;0;0},{1;1;3;4;1;4;7;7;9}),{1;2;3;4;5;6;7;8;9}),1))

(Notice that for the SUBTOTAL/OFFSET part of the formula, 1 is returned for each cell that is visible and 0 for each cell not visible.)

=SUM(IF(FREQUENCY({1;1;3;FALSE;1;FALSE;7;FALSE;FALSE},{1;2;3;4;5;6;7;8;9}),1))

=SUM(IF({3;0;1;0;0;0;1;0;0;0},1))

=SUM({1;0;1;0;0;0;1;0;0;0})

=3

For the second part of your question, convert the data into a list...

Data > List > Create List

The ranges will automatically adjust as data is added or removed.

Hope this helps!

Edit: Corrected the evaluations...

Re: Count Uniques In Visible Cells

You're very welcome! Please note that I corrected some of the evaluations, as I've indicated in my previous post. Hopefully, you noticed I made the corrections.