SOLVED

COUNT ID Codes IF Columns A & B respect specific criteria

Occasional Contributor

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 CodeBalance in $USD
247627537935,268,847,385.00
5207551055205,721,030.00
935951580891,720,392,066.00
247627537938,491,785,509.00
396703241442,833,118,004.00
86634894620.00
493199721267,749,503,521.00
295191768523,981,235,402.00
283018399864,626,847,523.00
100555392394,602,840,726.00
23746532310.00
747804049288,095,379,658.00
100555392328,086,717,554.00
948887646478,954,674,545.00
798086803481,063,980,379.00
842844650848,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!

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?

@antotom98 

Revised it to an all-in-one formula using HSTACK, BYROW and LAMBDA. See it that works for you.

Thanks a lot for that!
Would eventually be possible to get the final total number in just one cell?

@antotom98 

Not sure I follow. Perhaps I misunderstood you completely. If the Final Total equals 14, we could just count all the non-zeroes in the USD column.

=COUNTIF(Table1[Balance in $USD],"<>0")

 

Or else, what total would you expect to see from your example data?

@Riny_van_Eekelen sorry for the misunderstanding, actually I should have explained myself better. I just need to fill the bottom table in a dashboard, and I was trying to build a unique formula that gives me the result taking into account the parameters reported above.

 

ID CodeBalance in $USD
247627537935,268,847,385.00
5207551055205,721,030.00
935951580891,720,392,066.00
247627537938,491,785,509.00
396703241442,833,118,004.00
86634894620.00
493199721267,749,503,521.00
295191768523,981,235,402.00
283018399864,626,847,523.00
100555392394,602,840,726.00
23746532310.00
747804049288,095,379,658.00
100555392328,086,717,554.00
948887646478,954,674,545.00
798086803481,063,980,379.00
842844650848,394,944,086.00

 

SUMMARY TABLE
Total number of ID Codes
?

 

 

best response confirmed by antotom98 (Occasional Contributor)
Solution

@antotom98 

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.