Mar 13 2021 08:31 AM
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 |
Mar 13 2021 08:48 AM
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))
Mar 13 2021 10:30 AM