

August 8, 2020

Hi..
I am having trouble filtering a column (assg_id) and remove the duplicate numbers. The formula that I am using is =Unique(A2:A2669) but then I get a spill error..
I want to count the assignment number one time even if the same assignment number appears on the list multiple times.


July 16, 2010

Hi Leslie,
Select cell F3 to the end of the cells containing data and press DELETE. This will fix the spill error. You only enter dynamic array formulas in a single cell and then Excel doesn't the copying (spilling) down.
If you only want the count, you can wrap the UNIQUE formula in COUNTA e.g.
=COUNTA(UNIQUE(C$2:C$2669))
Notice I removed the TRUE argument from UNIQUE because TRUE will only return unique items, i.e. assg_ids that only appear once, as opposed to a distinct list of assg_ids. More on the UNIQUE function here.
Mynda
1 Guest(s)
