Unique value with criteria (Frequency formula)

Copper Contributor

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!

 

IDWays of Giving Total Giving 
1000  $                  20.00
1001Online1 $                100.00
1002Online2 $                  50.00
1003  $                100.00
1001Online1 $                  35.00
1001Online2 $                  10.00

 

2 Replies

@Elva_Tanguerre 

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))

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