Excel IF and COUNT functions

[Robin Faulkner]

I need to write an IF statement that look in the first column for a set value
and then counts the number of repetitions in column two. Column two contains
dates which contain duplicates. The statement needs to count dupicate dates
as single entries.

 

[Jason Morin]

Try:

=SUM(N(FREQUENCY(IF(A1:A100=E1,MATCH
(B1:B100,B1:B100,0)),MATCH(B1:B100,B1:B100,0))>0))

Array-entered, meaning press ctrl + shift + enter, where:

A1:A100 = 1st column
B1:B100 = 2nd column (dates)
E1 = value to look for in 1st column

HTH
Jason
Atlanta, GA

 

[Aladin Akyurek]

=COUNT(1/FREQUENCY(IF(($B$1:$B$100<>"")*($A$1:$A$100=E1),MATCH($B$1:$B$100,$B$1:$B$100,0)),ROW(INDEX($B$1:$B$100,0,0))-ROW($B$1)+1))

which must be confirmed with control+shift+enter instead of just with enter.