Counting Unique Donors in a Given Year

[mtaylor50]

I know there are bundles of board postings regarding counting with multiple criteris, and I'm sorry about my inability to find the one that will work for me (after 3 hours of trial and error).

My formula needs to count unique individuals who donated $ to a charity in 2005 (column B lists donors' names; column c shows the year of their gift, after being extracted from a date field elsewhere).

Here's the formula that counts the unique donors regardless of date:

=SUMPRODUCT(($B$2:B$3001<>"")/COUNTIF(B$2:B$3001,B$2:B$3001&""))

I just can't seem to amend it to so it counts only 2005 donors, but only once, i.e., if donor gave in 2004 and 2005, the formula needs to count this donor as a 2005 donor; 2004 is irrelevant for the 2005 count. Also if same donor gave twice in 2005, donor is counted only once in the 2005 count.

Many thanks!
Mark

 

[Aladin Akyurek]

If you download and install the latest version of the morefunc.xll add-in:

=COUNTDIFF(IF($C$2:$C$3001=2005,$B$2:$B$3001,"#"),FALSE,"#")

which must be confirmed with control+shift+enter.

 

[mtaylor50]

Works great (the many attempts I tried weren't even close)!
Thank you very much, Aladin.