Using COUNTIFS, and/Or
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!
Answers
-
Hope you are fine, Could you please add a screenshot for your sheet.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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"?
-
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!!
-
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)
-
Hm it seems to be counting them all even if one of the columns is checked @Paul Newcome any ideas?...
-
My apologies. I misread your initial post.
=IF(OR([Completed (AP)]@row = 1, [Invite Sent]@row = 1, [Will Not Use]@row = 1), 1)
-
Thank you so much @Paul Newcome !
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!