# Using COUNTIFS, and/Or

Options
✭✭✭✭✭✭

Hello!

I am trying to total anything from a sheet that is BLANK in "Supplier Email" AND Priority is HIGH

AND, IF any of the following are checked:

• Completed (AP)
• Invite Sent
• Will Not Use

Using the formula below, it doesn't seem to work.

=COUNTIFS({Supplier Email}, <>"", {Priority}, [Total Suppliers]@row, (OR{Completed (AP)}, 0, OR{Invite Sent}, 0, OR({Will Not Use}, 0,)

Any help is appreciated! Thank you!

• ✭✭✭✭✭✭
Options

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Are the three checkboxes separate columns?

I also notice that your description indicates "Supplier Email" is blank, but your formula indicates "Supplier Email" is not blank.

Is it safe to assume that the data in [Total Suppliers]@row is "HIGH"?

• ✭✭✭✭✭✭
Options

AH yes @Paul Newcome - you are right on both of those:

I want the "Supplier Email" NOT Blank and yes "total suppliers" is "HIGH" Here is a screen shot attached

Thank you!!

• ✭✭✭✭✭✭
Options

I would suggest inserting a helper column that can later be hidden. A checkbox column would be easy enough. In this helper column you would use an IF/OR formula to check the box if it meets any of the three checkbox criteria. Then you would reference this helper column in your cross sheet formula.

=IF(OR([Completed (AP)]@row <> 1, [Invite Sent]@row <> 1, [Will Not Use]@row <> 1), 1)

=COUNTIFS({Supplier Email}, <>"", {Priority}, [Total Suppliers]@row, {Helper Column}, @cell = 1)

• ✭✭✭✭✭✭
Options

Hm it seems to be counting them all even if one of the columns is checked @Paul Newcome any ideas?...

• ✭✭✭✭✭✭
Options

=IF(OR([Completed (AP)]@row = 1, [Invite Sent]@row = 1, [Will Not Use]@row = 1), 1)

• ✭✭✭✭✭✭
Options

Thank you so much @Paul Newcome !

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!