Need help on Countif with OR & AND in report filter

VenuG

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

Paul Newcome

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.

VenuG

Hi Paul,

Thank you for your reply, Tried but no luck.

Thanks,

Paul Newcome

What did you try and what was the result?

VenuG

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,

Paul Newcome

What formula did you use?

VenuG

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

Paul Newcome

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")

VenuG

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}, ""))

Paul Newcome

Which range is the helper column?