What is wrong with my COUNTIF formula?

Jo-Ann Turning

I am not sure what is wrong with my very simple formula...

=COUNTIF(Quarter:Quarter, "Q1")

I have a list of 700 line items and am trying to add formula to total up a count of programs 'by quarter' in sheet summary and keep getting #imparseable or #invalid data type (if I add "quotes" outside Q1)

I am creating a new field for Q1, Q2, Q3, Q4 so I can have totals for each quarter to add this data to a dashboard.

Please help - see attached screen shot so you can see a part of my data. (Only other detail that might be important - this "Quarter" column is autocalculated based on the dates that are added in other columns. Not sure that should make a difference, but thought I'd mention it. I was able to successfully COUNTIF other columns that were populated in the same way based on data that was entered in other columns. But I've spent way too much time trying to trouble shoot and I'm sure it's some easy obvious solution that I am missing.

thank you for your help.

Paul Newcome

Are you getting an error or are you getting an incorrect count using the formula in your original post?

What is the formula you are using to populate the Quarter column?

Jo-Ann Turning

I get an error that shows the word "unparseable" or "invalid data type"

this is the formula:

="Q" + IF(MONTH([Start Date]@row) >= 10, "4 ", IF(MONTH([Start Date]@row) >= 7, "3 ", IF(MONTH([Start Date]@row) >= 4, "2 ", "1 ")))

Paul Newcome

Try this...

=COUNTIF(Quarter:Quarter, "Q1 ")

Notice the space after the 1 and before the closing quote. The formula you are using in your Quarter column is generating a

"Q#space"

But your COUNTIF in your original post is searching for

"Q#"

Jo-Ann Turning

darn, that didn't work either. I'm so frustrated!

Paul Newcome

Can you show a screenshot of the formula in the sheet similar to the screenshot below?

Jo-Ann Turning

I've been trying to add the image in a reply for the last half hour and it is dumping me into a continual loop where I select the picture and then it never pops in - and the pictures window comes up again for me to select - over and over and over.... I give up. I have even restarted my computer and tried again. I am not meant to figure this out today. Thanks for your help.

Is it possible to pay for hourly support or something like that to get help directly on the phone with anyone from smartsheet? I feel like there is an easier way to accomplish what I am trying to do. Someone with the expertise would probably be able to help me in less than a hour - and it's taking me all day to figure some things out. thanks for letting me know.... I can reach out to my corporate account manager too if that's best.

Andrée Starå

Hi @Jo-Ann Turning

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)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jo-Ann Turning

Ok thank you. I just shared this sheet to you. Appreciate your help taking a look.

I'm sure it's something simple that I am missing..... Jo-Ann