Let’s say I’ve got two columns for arguments sake. In column A there is always TEXT (actually a reference number with Alpha/Numeric) and in column B there are blanks and non-blanks of text.
I want to do a couple of things. Firstly, I want to count everything in column A, regardless of what’s in column B. However, entries in column A may be repeated according to what is in column B – so I need to use advanced filter and ask column A to display “Unique Records” only – I trust that this is correctly returning only one example of each.
Using COUNTA isn’t sophisticated enough because that will count hidden cells and I don’t want those included.
So, I am using this convoluted formula, which seems crazy. I just want to count VISIBLE ROWS as unique entries according to whether I expand or contract the rows!
A* denotes Column A, (row number) e.g. A2:A500.
That’s one hell of a formula just to get Excel to count 1,2,3,4...! But it does actually count something!
The second problem occurs when I want to count only the number of entries in column A with non-blanks in column B – if I use the AUTOFILTER on column B it will change that column, but the rule for UNIQUE RECORDS on column A is ignored, so I might get duplicates returning a false count. That and the formula at the foot of column A disappears. Help!
What’s the solution? How can I count unique records only, including blanks and then non-blanks without getting a false result and the formula must remain in place, recalculating every time the column expands or contracts? It must be a formula that counts TEXT entries, not values.