Counting Unique Donors in a Given Year

mtaylor50

New Member
Joined
Oct 4, 2004
Messages
28
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Oct 4, 2004
Messages
28
Works great (the many attempts I tried weren't even close)!
Thank you very much, Aladin.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,380
Members
412,589
Latest member
ArtBOM
Top