How to count unique/ distinct visible values in a filtered column, with considering blank cells?

2
votes

As such references I had found, those are not covering "assuming blank cells same as non-blank ones" matter.

I had found this array formula: (Ref: extendoffice.com)

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D2,ROW(D2:D22)-ROW(D2),,1)), IF(D2:D22<>"",MATCH("~"&D2:D22,D2:D22&"",0))),ROW(D2:D22)-ROW(D2)+1),1))

Any guides are appreciated.

Update

enter image description here

F22 result of calculating for Table1[Column1]

G22 result of calculating for Table1[Column2]

H22 result of calculating for Table1[Column3]

I want the Formula returns: G22=4 & H22=1

Note: My table has filtered range and I calculating visible values.

1
excelexcel-formula
what do you mean "with considering blank cells"? Can you give an example? Does it stop calculating at the first blank cell in the range? - QHarr
Means: 1) the column may contained no blank cells. 2) the column may contained few items blank cells. 3) the column may contains all the cells are blank cells. I need in case1: add 0 to the result of example referred formula in the original question; case2: add 1 to the result of example referred formula in the original question; case3: add 1 to the result of example referred formula in the original question. Regards. - Tuberose
Could you share some example data in an edit such as what you're formula is returning, and what you need to return? (More info in: minimal reproducible example) - ashleedawg
Does it stop calculating at the first blank cell in the range?: If reached the first blank cell, add 1 to the result and do continue calculating, (act with blank cells, same as another values cells.) - Tuberose

1 Answers

3
votes

In the formula you quote the SUBTOTAL part is used to only consider visible cells.......but it also ignores blanks, so if you want to include blanks as another distinct value to be counted that's a problem.

Do you have any column that you know will be fully populated (e.g. column A)? If so you can base the SUBTOTAL part on that column and the counting distinct on the actual column in question, e.g. assuming A2:A22 will always be fully populated try this version to count distinct values in D2:D22 (including blanks):

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

confirmed with CTRL+SHIFT+ENTER