Count Function not working

Mashall Greene

Hello,

I am using a COUNTIF function as shown in the screenshot attached, and it is grabbing the "Complete - Handover to VAR" Status field, but not the "ONB - No Reply by VAR" status field.

There is some conditional formatting applied, but I'm not sure what the formulae isn't calculating the ONB - No Reply by VAR field.

Any idea why the formula isn't taking this field is there some setting in Smartsheet that may be blocking this?

Paul Newcome

It looks like you might have 2 spaces after ONB in the formula but only one space in the data you are evaluating.

Mashall Greene

Hey Paul,

Thank you for the quick reply, unfortunately that was not the issue as it is also for the value "Local Sales ONB".

Not sure why these are not getting counted any other ideas?

Paul Newcome

Double check the column data as well. Smartsheet will only display one space even if there are multiple spaces, but it can affect your counts when you start referencing the data in formulas.

The two cells in the below screenshot are a copy/paste of each other to show what I mean. The data itself has five spaces between the letters, but when I am not actively editing the data only one space is shown.

I am also not sure what you mean by "also for the value "Local Sales ONB"". I don't see that in your formula or anywhere in the screenshots.

Mashall Greene

Thanks Paul, it doesn't look like there are any extra spaces after the value in the drop-down, shown in the picture below for the "ONB - No Reply by VAR" field.

Other status fields that are unable to be counted are:

• Waiting for VAR Response - 0
• NS- Waiting for VAR Response - 0
• Local Sales ONB - 0
• Complete - Handover to VAR - 0
• No / Incorrect Email Address in PPE- 0
• Rejct-Deny-Exprd-Inactive in PPE - 0
• Not Submitted in PPE - 0
• Approved - No Sell-In Country - 0
• ONB - No Reply by VAR - 0
• PPE - No Reply by VAR - 0

Below please find some screenshots of filters run on two of these statuses pulling results, so it shows their numbers should be higher than 0.

Paul Newcome

@Andrée Starå Do you have any ideas on the above?

Mashall Greene

Thanks Paul and Andree, for some extra help

Approved - ONB to Start - 170 and koC Scheduled - 3 are the only fields in the status collumn that aree able to be counted.

Andrée Starå

@Paul Newcome

Strange!

Hmm!

@Mashall Greene

Can you try changing the column name(s) to one word and update the formula and try if that works?

Did it work?

Mashall Greene

Hey @Andrée Starå, thank you for the quick reply. Which columns? The columns in the COUNTIF formula is already one word, "Status".

Andrée Starå

@Mashall Greene

It's been a long day! 😉I meant the words that you're counting. Change all of them to single words to see if they work.

I'd recommend saving a copy of the sheet and test it in the copy.

Mashall Greene

Hey @Andrée Starå, thanks for the help, still not having much luck. Is there any additional information I can provide you that might help figure out a reason for this?

Andrée Starå

@Mashall Greene

Happy to help!

I'd be happy to take a quick look!

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Mashall Greene

Hey @Andrée Starå , This problem is fixed, not sure why but today formulas are grabbing, maybe a caching issue?

Paul Newcome

Glad it's working for you now. I have seen instances of complex/larger sheets taking a while to update correctly.

Andrée Starå

@Mashall Greene

Excellent!

Happy to hear that it's working!

I'm with Paul, that it probably could be because of a complex sheet.

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!