Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Overachievers Alumni
    Answer ✓

    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

  • Community Champion
    Answer ✓

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

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
    User: "Allan Z"
    Answered ✓
    9
    2
  • Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
    User: "stratman"
    Answered ✓
    15
    2
  • I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
    User: "m_anderson"
    Answered ✓
    13
    2