Need help with COUNTIFS and Count(Collect) formulas

Options

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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!