 # Unique value with criteria (Frequency formula)

Hello, in the table below I need to count how many distinct donors have given online (=3). The real file has 18,000 rows so I used FREQUENCY instead of COUNTIF as COUNTIF took too long to calculate unique values on such a big file.  Now I am stuck with a long formula and I do know know were or how insert criterias...

The array formula that I use to count unique values is:

=SUM(IF(FREQUENCY(IF(YTD!A2:A35000<>"",MATCH(YTD!A2:A35000,YTD!A2:A35000,0)),ROW(YTD!A2:A35000)-ROW(YTD!\$A\$2)+1),1))

Note that I am not very good in Excel and I tend to copy formulas from the web and twick them. Thanks!

 ID Ways of Giving Total Giving 1000 \$                  20.00 1001 Online1 \$                100.00 1002 Online2 \$                  50.00 1003 \$                100.00 1001 Online1 \$                  35.00 1001 Online2 \$                  10.00

2 Replies

# Re: Unique value with criteria (Frequency formula)

Shouldn't the result be 2 in your example? Only donors 1001 and 1002 have given online.

The following array formula confirmed with Ctrl+Shift+Enter should do what you want:

=SUM(--(FREQUENCY(IF(LEFT(YTD!B2:B35000,6)="Online",MATCH(YTD!A2:A35000,YTD!A2:A35000,0)),ROW(YTD!A2:A35000)-ROW(YTD!A2)+1)>0))

# Re: Unique value with criteria (Frequency formula)

Yes indeed, it should be equal to 2. Thanks for the answer!