Need help with COUNTIFS and Count(Collect) formulas
Hi Formula Gurus,
This should be straight forward but I keep getting 0 or 1. I'm looking for a count of projects do not have a Project Category of "test" and they have Year Started is 2023 (or yr 2024 in example 2) and Quarter Started is Q3 (or Q2 in example 2).
The first formula below results in 0 (zero).
This sheet is just as you see. It pulls from an Intake sheet.
The cells that have a 1 are using this Count(Collect formula
Appreciate your feedback!!!
Best Answers
-
The CONTAINS was my suggestion when I thought you were needing to pull 2023 out of a longer text string. CONTAINS works with text, so you have to have "" around the 2023 if that's what you're doing. However, you don't need that if you instead do:
=COUNTIFS({Project Category}, <>"test", {Project Intake Sheet Range 2}, 2023, {Project Intake Sheet Range 1}, "Q3")
To address your earlier comment, the column might say Text/Number, but text and numbers are treated differently in formulas. Depending on how the value gets into the cell, sometimes it's text and sometimes it's a number. For example your YEAR formula is giving you a number, but your COUNTIFS was using "2023" which is referring to text.
-
Sorry for not being more clear. My suggestion was simply using the COUNTIFS from your orignal post and just removeing the quotes from around 2023.
=COUNTIFS({Project Category}, <>"test", {Project Intake Sheet Range 2}, 2023, {Project Intake Sheet Range 1}, "Q3"))
Answers
-
Does Range 2 need to use a CONTAINS instead of just "2023"? In the second formula you're matching to "2024 - Opened" but in the first formula you just have "2023". So maybe either "2023 - Opened" or use a CONTAINS. To use CONTAINS you need to COLLECT, so:
=COUNT(COLLECT({Project Category},<>"test",{Project Intake Sheet Range 2},CONTAINS("2023",@cell), {Project Intake Sheet Range 1}, "Q3") -
Hi Brian,
I'm actually pulling the Year Started from the intake sheet. It only has the year in a text column. I want to populate this smaller sheet so I can create graphs for both years.
I tried your formula. It returned 1 for 2023, Q3. That is correct. However for Q4 2023, it only returned 1, when it should be 5. Any thoughts please?
-
Are you able to provide some screenshots of the source data?
-
@Paul Newcome - thank you! Here's a pic of the intake sheet. You can ignore the #Invalid Data Type in row1. It's due to the column having a column formula. The row is basically a header.
-
In your Year Started formula try adding +"" to the end of the formula (I assume it's something like
= YEAR([Onboarding Start Date]@row)so try=YEAR([Onboarding Start Date]@row)+""This will turn your Year value into Text which CONTAINS can then find.
Although it doesn't seem like you need the CONTAINS, so maybe just back to your COUNTIFS (which looked fine btw) and see if it works with the new text-based value for Year.
-
Hi Brian,
Thank you for your thoughts. The Year Started is already a text based column:
-
-
=YEAR[Onboarding Start Date]@row
-
Remove the quotes from around 2023 in your COUNTIFS.
-
@Paul Newcome, Thanks for the suggestion. Unfortunately, I am still not getting fully accurate results for the revised formula: =COUNT(COLLECT({Project Category}, <>"test", {Project Intake Sheet Range 2}, CONTAINS(2023, @cell), {Project Intake Sheet Range 1}, "Q2"))
Q2 should be 0 and Q4 should be 5, where Q1 and Q3 are correct.
-
Your syntax for the COLLECT function is off. Use the same syntax you have but instead of using COUNT/COLLECT, just use COUNTIFS.
-
@Paul Newcome - sorry, I'm getting confused. Getting Unparseable error.
=COUNTIFS({Project Category}, <>"test", {Project Intake Sheet Range 2}, CONTAINS(2023, @cell), {Project Intake Sheet Range 1}, "Q3"))
-
The CONTAINS was my suggestion when I thought you were needing to pull 2023 out of a longer text string. CONTAINS works with text, so you have to have "" around the 2023 if that's what you're doing. However, you don't need that if you instead do:
=COUNTIFS({Project Category}, <>"test", {Project Intake Sheet Range 2}, 2023, {Project Intake Sheet Range 1}, "Q3")
To address your earlier comment, the column might say Text/Number, but text and numbers are treated differently in formulas. Depending on how the value gets into the cell, sometimes it's text and sometimes it's a number. For example your YEAR formula is giving you a number, but your COUNTIFS was using "2023" which is referring to text.
-
@Brian_Richardson - Thanks so much for the explanation. It makes perfect sense and worked well.
@Paul Newcome - appreciate your advise as well.
-
Sorry for not being more clear. My suggestion was simply using the COUNTIFS from your orignal post and just removeing the quotes from around 2023.
=COUNTIFS({Project Category}, <>"test", {Project Intake Sheet Range 2}, 2023, {Project Intake Sheet Range 1}, "Q3"))
Help Article Resources
Categories
Check out the Formula Handbook template!
