Count unique values using countifs?

Question

I'm trying to create a gift table. Here is some sample data:

Gift    Donor ID
$1      100
$1      151
$2      100
$2      186
$2      124
$4      124
$4      178
$4      162
$5      100
$5      158

In column A I've written a formula that counts the number of gifts greater then $1 and less than or equal to $4. There are 6.

 =COUNTIFS(A2:A11,">1",A2:A11,"<=4")

Now what I need to do is count the number of unique donors from column B who meet the criteria from column A. In this case there are 5. What would that formula look like?

I'm not sure what that would look like. I just tried it without much success. — Michael Harris
Pivot table definitely the way to go. You can group the results if you want $5.00 and $5.01 to appear in the same row. — Denise Skidmore
Unfortunately in this case I'm the grunt creating the spreadsheet for the pro to analyze. Your link was helpful. I ended up creating a helper row and using the answer provided there. — Michael Harris

Unknown Unknown · Accepted Answer · 2014-10-31T21:49:09

If you prefer a conventional formula approach, you can perform this with an array formula version of a standard count unique with SUMPRODUCT.

=SUM(IFERROR((($A$2:$A$99>1)*($A$2:$A$99<=4))/COUNTIFS($A$2:$A$99,">1",$A$2:$A$99,"<=4",$B$2:$B$99,$B$2:$B$99&""),0))

Finalize with Ctrl+Shift+Enter rather than simply Enter.

Count unique values using countifs?

1 Answers