SOLVED

Which formula should I use?

Copper Contributor

Which formula should I use?

Hi all,

Which formula should I use to count the number of suppliers excluding duplicates and using the date filters highlighted in blue?
The result for each example should be the numbers highlighted in yellow.

Thank you.

2 Replies
best response confirmed by nessatm (Copper Contributor)
Solution

Re: Which formula should I use?

Shouldn't the 2nd example return 3? The non-zero suppliers are AAA, BBB and CCC.

In E5:

=COUNTA(UNIQUE(FILTER(\$I\$9:\$I\$24,(\$H\$9:\$H\$24>=E2)*(\$H\$9:\$H\$24<=E3)*(\$I\$9:\$I\$24<>0))))

Copy/paste to H5 and K5

Re: Which formula should I use?

Thank you so much! This is exactly what I need.
Correct, 2nd example result is 3. Sorry, my mistake :)

1 best response

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

Re: Which formula should I use?

Shouldn't the 2nd example return 3? The non-zero suppliers are AAA, BBB and CCC.

In E5:

=COUNTA(UNIQUE(FILTER(\$I\$9:\$I\$24,(\$H\$9:\$H\$24>=E2)*(\$H\$9:\$H\$24<=E3)*(\$I\$9:\$I\$24<>0))))

Copy/paste to H5 and K5