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!

Formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A*,ROW(A*:A*)-ROW(A*),0)),--(A*:A*<>””))

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.