Need help on Countif with OR & AND in report filter
I have following screenshot's report, but I couldn't figure out in formula using Countif formula,
I tried with below formula:
=COUNTIFS({ProjectSurveyContact1}, OR(HAS(@cell, "")), {ProjectSurveyContact2}, OR(HAS(@cell, "")), {ProjectSurveyContact3}, OR(HAS(@cell, "")), {ProjectStatus}, OR(@cell = "Closed", @cell = "Cancelled"), {ProjectManager}, <>"", {DeliveryClassification}, ="Project", {SalesCloseDate}, <TODAY(-30))
But above formula throwing '0' instead of numbers
2nd formula also tried here is:
=COUNTIFS({ProjectSurveyContact1}, ="", {ProjectSurveyContact2}, ="", {ProjectSurveyContact3}, ="", {SalesCloseDate}, <TODAY(-30), {ProjectStatus}, OR(@cell = "Closed", @cell = "Cancelled"), {ProjectManager}, <>"", {DeliveryClassification}, CONTAINS("Project", @cell))
But it throwing some uneven numbers, which are not realistic,
Here How to eliminate the duplicate values is my question
Please help me, Thanks in Advance
Answers
-
You would need a helper column on the source sheet that flags rows where one of the three contact columns is blank then evaluate this column in the COUNTIFS instead.
-
Hi Paul,
Thank you for your reply, Tried but no luck.
Thanks,
-
What did you try and what was the result?
-
Hi Paul,
Tried with helper column, and inserted the column in formula but, still the end result is coming 0, instead of counts (127), here I required a formula which can check all the conditions as per OR condition need to delete the duplicates, still working to get, but not getting the solutions, here required help from you.
Thanks,
-
What formula did you use?
-
Here it is
=SUM(COUNTIFS({ProjectSurveyContact1}, "", {SalesCloseDate}, <TODAY(-30), {ProjectManager}, <>"", {ProjectStatus}, NOT(OR(@cell = "Closed", @cell = "Cancelled"))) + COUNTIFS({ProjectSurveyContact2}, "") + COUNTIFS({ProjectSurveyContact3}, "") - COUNTIFS({ProjectSurveyContact1}, "", {SalesCloseDate}, <TODAY(-30), {ProjectManager}, <>"", {ProjectStatus}, NOT(OR(@cell = "Closed", @cell = "Cancelled"))) - COUNTIFS({ProjectSurveyContact3}, "") - COUNTIFS({ProjectSurveyContact2}, ""))
helper column formula has not saved, as it is also producing the same as like above end result
-
Instead of NOT(OR(.........)) try just an AND with "not equal to" arguments.
NOT(OR(@cell = "Closed", @cell = "Cancelled"))
changes to
AND(@cell <> "Closed", @cell <> "Cancelled")
-
Still getting as 0
=SUM(COUNTIFS({ProjectSurveyContact1}, "", {SalesCloseDate}, <TODAY(-30), {ProjectManager}, <>"", {ProjectStatus}, AND(@cell <> "Closed", @cell <> "Cancelled")) + COUNTIFS({ProjectSurveyContact2}, "") + COUNTIFS({ProjectSurveyContact3}, "") - COUNTIFS({ProjectSurveyContact1}, "", {SalesCloseDate}, <TODAY(-30), {ProjectManager}, <>"", {ProjectStatus}, AND(@cell <> "Closed", @cell <> "Cancelled")) - COUNTIFS({ProjectSurveyContact2}, "") - COUNTIFS({ProjectSurveyContact3}, ""))
-
Which range is the helper column?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!