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!