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

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

Need help with COUNTIFS and Count(Collect) formulas

Answers

Kathy PPT ✭✭✭✭

07/15/24 edited 07/15/24

0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula need to return Pass/Fail if 1 of multiple cells are blank
Hi, I am trying to put a formula together that will provide a fail if any part of the address or the phone number hasn't been provided. The below formula only provides fail if all cells are blank. I have tried other variations, this is the only one that does not give me some sort of error. =IF(OR(NOT(ISBLANK([Street…
Combining AVG/COLLECT/NETWORKDAYS
I am attempting to calculate the average number of working days it takes to complete a request, Criteria I want to base the data collection on include: urgency level Month and Year(I created a rolling month chart to see trends-see below screenshot) The Work Completion Date determines the month it will be reported on This…
CountIf Function
I want to count the number of each option in this example; Change Router, Upgrade, Disconnect. My Objective column is set up as a drop down with allow multiple values enabled. My formula is only calculating Row 5, because that is the only row with only one value. How do I get my formula to calculate each drop down option…

Need help with COUNTIFS and Count(Collect) formulas

Answers

Help Article Resources

Categories