Calculate Supplier Information

Kelly R.

Hello,

I'm stuck with a formula...I am trying to calculate the number of suppliers with open complaints greater than 3. Any ideas?

Thanks,

Kelly

Paul Newcome

You would need to insert a helper column that has this column formula:

=IF(COUNTIFS([Supplier Name]:[Supplier Name], @cell = [Supplier Name]@row, [Request Status]:[Request Status], @cell = "Open") > 3, 1)

Then to get how many suppliers have more than 3 open you would use something like this:

=COUNT(DISTINCT(COLLECT([Supplier Name]:[Supplier Name], [Helper Column]:[Helper Column], @cell = 1)))

Paul Newcome

You would need to insert a helper column that has this column formula:

=IF(COUNTIFS([Supplier Name]:[Supplier Name], @cell = [Supplier Name]@row, [Request Status]:[Request Status], @cell = "Open") > 3, 1)

Then to get how many suppliers have more than 3 open you would use something like this:

=COUNT(DISTINCT(COLLECT([Supplier Name]:[Supplier Name], [Helper Column]:[Helper Column], @cell = 1)))

Kelly R.

Thank you, Paul. Both Formulas helped greatly! Have a great day!

Thanks,

Kelly

Paul Newcome

Happy to help. 👍️