Does the COUNTIF Function work if the criterion have decimal places?

Alexa Rice

I am working on an extended process and I want to sub-categorize a set of steps. Ideally I want to use the 1.1, 1.2, 1.1.1 as step headers but when I try and do functions to count cells it is not working.

Example:

The COUNTIF functions work for the whole numbered entries but will not pull with the decimal entries. Here are the two formulas I am using:

Good formula:

=COUNTIF([Current Pipeline Stage]:[Current Pipeline Stage], "3 engaged")

Broken formula:

=COUNTIF([Current Pipeline Stage]:[Current Pipeline Stage], "4.1 Drafting")

Any suggestions on how I can get those formulas happy again?

Thanks!

Lex

Paul Newcome

What do you mean by "broken"? Is it giving you an error or an unexpected result?

Alexa Rice

An unexpected result. Sorry for the confusion :). If I remove the decimal place in the column and return the COUNTIF criterion to the whole rounded number it counts correctly (count = 14) . When I go in and replace the decimal in the column/formula then return is '0'

Paul Newcome

And it is manual entry? That's odd that the decimal is throwing it off. This may seem simple, but have you double checked your spelling in both places to ensure they are an exact match all the way down to the number and location of spaces and whatnot?

Alexa Rice

You were right - it was a spacing issue. 1 versus 2 spaces input. Thanks!

Paul Newcome

Happy to help! 👍️

One thing that I am not too fond of is that the cell data removes extra spaces when displayed, but keeps the extra spaces on the back end.

If you type in "text" space space "text", then the data stored will have those two spaces and any functions/features/formulas will read those two spaces, but the cell will only show one space, so you have no idea that second space is even there.

If I am not mistaken, I believe it works the same way here in the Community. I am putting five spaces between each word in this sentence to test it .

Paul Newcome

I did actually put five spaces between each word in that last sentence, but as you can see, it only shows one between each word. It does the same in the sheets...