How do I do count calculations ignoring duplicate values

R

Robin Faulkner

I have a calculation which is roughly "=count(if(A1:A100=F5,B1:B100)" where
F5 is a set value, column A is a list of values and column B is a list of
dates. I need the calculation to count the number of dates, but if the date
is duplicated I need the calculation to recognise its value only once.
 
A

AlfD

Ensure that the data is sorted in date order.
Add a helper column (P, say) and in P2put =if(O2=O1 then 1,"") and copy
this down.
A duplicate date will then have a 1 against it.
You can then either use Autofilter to view those with a blank in P
(remember that the Autosum value inthe bar at the bottom of the screen
only counts visible cells); or you can re-sort by col P to keep the
"blanks" at the top and just work on those.

Alf
 
Top