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
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!