Forum Discussion
COUNT ID Codes IF Columns A & B respect specific criteria
Hello there!
I'm working on a table, and I need to count the number of ID Codes depending on 2 specific criteria:
1) Each ID Code has to be counted only once, while as you can see from the ID Codes in bold there can be duplicates
2) The Balance in $USD has to be different from zero, so I need to count every positive and negative number.
| ID Code | Balance in $USD |
| 2476275379 | 35,268,847,385.00 |
| 5207551055 | 205,721,030.00 |
| 9359515808 | 91,720,392,066.00 |
| 2476275379 | 38,491,785,509.00 |
| 3967032414 | 42,833,118,004.00 |
| 8663489462 | 0.00 |
| 4931997212 | 67,749,503,521.00 |
| 2951917685 | 23,981,235,402.00 |
| 2830183998 | 64,626,847,523.00 |
| 1005553923 | 94,602,840,726.00 |
| 2374653231 | 0.00 |
| 7478040492 | 88,095,379,658.00 |
| 1005553923 | 28,086,717,554.00 |
| 9488876464 | 78,954,674,545.00 |
| 7980868034 | 81,063,980,379.00 |
| 8428446508 | 48,394,944,086.00 |
Now, of course I can filter the 0s out of the Balance in $USD column, and then use remove duplicates, but I was trying to apply this process with a formula, to make it more efficient, since the actual file I'm working on is composed by thousands of rows. I tried with COUNTIFS, UNIQUE, and various combinations, but I didn't manage so far. Would you have any idea please?
Thanks a lot in advance!
What should your answer be in this example?
Perhaps this:
=COUNT(UNIQUE(FILTER(Table1[ID Code],Table1[Balance in $USD]<>0)))
It will return 12.
7 Replies
antotom98 Perhaps like in the attached file?
Hello Riny_van_Eekelen
Thanks a lot for your reply! That works, but I'm actually trying to do it with just one formula. Are you aware of any formula that might perform that?
Revised it to an all-in-one formula using HSTACK, BYROW and LAMBDA. See it that works for you.
