# Calculate Supplier Information

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

Thanks,

Kelly

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!