SOLVED

# COUNT ID Codes IF Columns A & B respect specific criteria

Copper Contributor

# 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?

7 Replies

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

@antotom98 Perhaps like in the attached file?

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

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?

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

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

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

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

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

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?

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

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

 SUMMARY TABLE Total number of ID Codes ?

best response confirmed by antotom98 (Copper Contributor)
Solution

# Re: COUNT ID Codes IF Columns A & B respect specific criteria

Perhaps this:

=COUNT(UNIQUE(FILTER(Table1[ID Code],Table1[Balance in \$USD]<>0)))

It will return 12.

1 best response

Accepted Solutions
best response confirmed by antotom98 (Copper Contributor)
Solution