 # Count Uniques In Visible Cells

• Hi all,

I have attached a sample sheet. Can someone be kind enough to teach me how to count unique numbers of visible cells ( I would be applying filter in the table) in textbox 1; and subtotal of the visible list in textbox 2?

Right now the range to count unique numbers is set to A2:A15, but I want to be able to change that range to other ranges, like B2:b100, etc. I do not know VB. Thank you!

## Files

• 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

Do I have any option other than using CSE formula? I am looking for an automatic formula. Thanks.

• 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

Hello Domenic and Dave, thank you both for sending me the formula and the codes, I will try them both and let you know. Thanks.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hello Domenic, your formula works great thank you very much, but that is one of the most scariest formula I have seen in life. If you can explain something about the formula you used I could use it with understanding as well (or atleast the functions that I need to learn to mix up those formulas). In the sample sheet, the range is set to \$a\$2:\$a\$100. Can you please tell me how I can change it to a dynamic range; my actual range in my workbook starts from \$c\$6. Thanks.

Dave: The pivot table is great but I have never used pivot table. Before asking question to you on the way you used it in my case I will do some reading and come back, it does give answers correctly. Or, if you wouldn't mind giving the tutorial yourself, that would be great ;). Thank you very much.

• 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

Hope this helps!

Edit: Corrected the evaluations...

• Re: Count Uniques In Visible Cells

Hello Domenic and Dave, thank you both for the tutorial/explanation. Your help is very much appreciated.

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

• Re: Count Uniques In Visible Cells

Yes, I noticed Domenic. Thank you ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!