COUNTIFS Formula Returning 0

KGetchellTiffany

I am working a new summary sheet for metrics that will be displayed on a Dashboard. I am having issues with my COUNTIFS formula returning a 0.

Current formula:
=COUNTIFS({Projects_MASTER Range 1}, CONTAINS("Exterior Refresh", @cell), {Projects_MASTER Range 2}, NOT(ISBLANK(@cell )), {Projects_MASTER Range 3}, "1", {Projects_MASTER Range 4}, "2025")

WHERE:

Projects_MASTER Range 1 = [PROJECT Name]
Projects_MASTER Range 2 = [PERMIT Approved]
Projects_MASTER Range 3 = [Projected Invoice Quarter]
Projects_MASTER Range 4 = [Projected Invoice Year]

I am trying to count the number of projects that contain "Exterior Refresh" in the Name, the Permit has been Approved (contains a date), the Projected Invoice Quarter is 1, and the Projected Invoice Year is 2025.

What am I missing here?

Thanks in advance!!!

prime_nathaniel

move contains to a helper column on your target that triggers a true false to true when exterior refresh is in the text. Ideally you should not infer project types with string parsing, best practice would be that type is its own column somewhere.

I also recommend you name references with object notation of what it is you are referring

Projects_MASTER Range 1 = [PROJECT Name]

would be more easily written as project.name, now it is obvious whenever you use the reference what it is.

Your formula should be much easier to write now.

Worst case you can use count(collect()), but this will use more comparisons and slow performance / limits.

KGetchellTiffany

@prime_nathaniel Thank you for the feedback. Moving Contains to a helper column did not help. Still resulting in 0.

prime_nathaniel

=COUNTIFS({project.exterior_flag}, true, {project.permitted}, >="2000-01-01"), {project.quarter}, 1, {project.invoice_year}, 2025)

Fix your refences to match these and eliminate your quotes against numeric values.

Note: I don't quite get your invoice approved column as it says 2000-01-01, maybe thats intentional maybe its not, if it is intention this works as is, if it isn't then just adjust to whatever floor cut off date you want to be considered.

KGetchellTiffany

@prime_nathaniel Can you please clarify project.exterior_flag?

Paul Newcome

You do not need a helper column for the CONTAINS. Trying to use a helper column for CONTAINS can prove to be very messy if you have a lot of different variables you need to break out your metrics for. You can use CONTAINS directly in the COUNTIFS just as you already have with no issues.

I would start with removing the quotes from around "1" and "2025".

You also wouldn't want to use the date range criteria being greater than "2000-01-01" as putting quotes around the string creates a text value which doesn't work for being compared by a greater or less than argument. If you want to compare it to a specific date such as greater than Jan 1, 2000, you would need to use a DATE function.

DATE(2000, 1, 1)

But in your original post you specified just having a date, so the NOT(ISBLANK(@cell)) should be fine.

If just removing the quotes from around the numbers doesn't work, try breaking it down. Write separate COUNTIFS for each {Cross Sheet Reference} and see which (if any) give you the zero count.

prime_nathaniel

KGetchellTiffany

That would be the helper column, it allows you to break your functions into smaller pieces. Because your criteria is now just a true false. You can always replace it later. The dot naming convention is just so you know what data you are getting from the name of the reference without needing to check the manage references UI

KGetchellTiffany

@Paul Newcome I broke down the formula and the "Projected Invoice Quarter" range is what is triggering the 0.

I also added a helper column in the source sheet where if "Survey RECEIVED" is not blank, then the new "Survey Complete" helper column returns Yes.

=COUNTIFS({PROJECT Name}, CONTAINS("Exterior Refresh", @cell), {Survey Complete}, "Yes", {Projected Invoice Quarter}, 1, {Projected Invoice Year}, 2025)

If I remove the {Projected Invoice Quarter}, 1 string from the formula, it works with what criteria remains.

KGetchellTiffany

Got it! Changing the prompt for {Projected Invoice Quarter} to include the @cell did the job!

=COUNTIFS({PROJECT Name}, CONTAINS("Exterior Refresh", @cell), {Survey Complete}, "Yes", {Projected Invoice Quarter}, @cell = "1", {Projected Invoice Year}, 2025)

Paul Newcome

Great. We've got it narrowed down to that. The next simplest test would be to include it in the formula but put quotes around the "1".

Paul Newcome

@KGetchellTiffany For future reference… I don't think it was the @cell reference. I think it was the quotes you added around the "1". We can confirm this by looking at the formula that is populating that column in the source data.

KGetchellTiffany

@Paul Newcome No, removing the quotes didn't change the result.