formula help

L_123

I am attempting to get a formula to count a range if the column next to it has the correct category. for example

DF  1.1

DV  2.1

DF  3.3

DF  1.2

DV  2.1

I want to count how many numbers lay between 1 and 2 in the category DF, and am struggling to get the program to do this.  Any help would be appreciated.

 

Brett Wallace

Hey Luke,

Have you tried the CountIF function? I know I use the formula below to count how many projects are in "Red"

 

=COUNTIF(Status3:Status90, "Red")

Brett Wallace

I am sorry I don't really understand what you are trying to count, or I would come up with a formula to help. But my example should be a good template for you.

L_123

That would work if there wasn't a range.  Using a range throws logic into it that I haven't been able to get the countif function to accept so far. Thanks though

Mike Wilday

See if this works... replace primary column with your column name. (Remove brackets [ ] if there are no spaces in the column name]

=COUNTIF([Primary Column]:[Primary Column], OR(LEFT(@cell, 4) = "DF 1", LEFT(@cell, 4) = "DF 2"))

https://help.smartsheet.com/articles/2476491/create-efficient-formulas-with-at-cell

https://help.smartsheet.com/articles/775363-using-formulas#left

L_123

Thanks. I always seem to have problems with the countif function. I appreciate the help!

Mike Wilday

You are welcome. Formulas are tricky, but over time they will grow on you. The nuances of Smartsheet will do the same.