Need help with COUNTIFS and Count(Collect) formulas

Kathy PPT

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!!!

Brian_Richardson

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.

Paul Newcome

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

Brian_Richardson

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

Kathy PPT

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?

Paul Newcome

Are you able to provide some screenshots of the source data?

Kathy PPT

@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.

Brian_Richardson

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.

Kathy PPT

Hi Brian,

Thank you for your thoughts. The Year Started is already a text based column:

Paul Newcome

What is the formula in the [Year Started] column?

Kathy PPT

=YEAR[Onboarding Start Date]@row

Paul Newcome

Remove the quotes from around 2023 in your COUNTIFS.

Kathy PPT

@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.

Paul Newcome

Your syntax for the COLLECT function is off. Use the same syntax you have but instead of using COUNT/COLLECT, just use COUNTIFS.

Kathy PPT

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

Brian_Richardson

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.

Kathy PPT

@Brian_Richardson - Thanks so much for the explanation. It makes perfect sense and worked well.

@Paul Newcome - appreciate your advise as well.

Paul Newcome

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