=COUNTIF([ARRIVAL TIME]:[ARRIVAL TIME],WEEKNUMBER[ARRIVAL TIME]@ROW = WEEKNUMBER(TODAY()))

Sandi Joralemon

Hello,

I am trying to use the formula above in the sheet summary to count all entries on a sheet that were entered in the current week. It returns as unparseable. The same error returns for month. Can anyone tell me what I am doing wrong here? Thanks very much!

Kelly Moore

Hello @Sandi Joralemon

You have capitalized your @row . Both @row and @cell must be written in lower case.

Try this.

=COUNTIF([ARRIVAL TIME]:[ARRIVAL TIME],WEEKNUMBER[ARRIVAL TIME]@row = WEEKNUMBER(TODAY()))

cheers

Sandi Joralemon

Hello @KDM,

Thank you for the response. I did know about that. I just typed in caps for the title. Sorry. However, I did try it again and I still get #unparseable. I typed it with a case sensitive column name as well as a lower case @row. I tried @cell as well to see if that made a difference, but it didn't.

=COUNTIF([Arrival Time]:[Arrival Time],WEEKNUMBER[Arrival Time]@row = WEEKNUMBER(TODAY()))

Arrival Time is actually the Created (Date) field and is datetime. I thought it might help to use the DATEONLY function, so I tried:

=COUNTIF([Arrival Time]:[Arrival Time],WEEKNUMBER(DATEONLY([Arrival Time])@row = WEEKNUMBER(TODAY()))

result: #unparseable.

I appreciate the advice.

Kelly Moore

Hey @Sandi Joralemon

It looks like you are missing parentheses around first weeknumber reference.

=COUNTIF([Arrival Time]:[Arrival Time],WEEKNUMBER([Arrival Time]@row) = WEEKNUMBER(TODAY()))

Sorry I didn't catch it yesterday.

As a personal preference of mine, I always write the singular/plural formulas Countif/Countifs , Sumif/Sumifs, etc in the plural form so that I can add terms to the formula as needed without any thought to re-write the formula. The plural version always works, regardless if only a single criteria is present, the singular form only works if a single criteria is present. fyi

cheers,

Kelly

Sandi Joralemon

Hello @KDM,

Thank you again. I am still getting the #unparseable. I have many options on this formula and I can't figure out why it isn't working. I appreciate your efforts and the quick tip!

Kelly Moore

Hey @Sandi Joralemon

Would you mind providing a screenshot of your actual formula. I want to see the colored parentheses and text within your formula.

Kelly Moore

=COUNTIFS([Arrival Time]:[Arrival Time], WEEKNUMBER(DATEONLY([Arrival Time]@row)) = WEEKNUMBER(TODAY()))

you could try this. I corrected your original parentheses.

Kelly

Kelly Moore

@Sandi Joralemon

Did you get your formula working?

Sandi Joralemon

@KDM,

I wasn't able to work last week. The Smartsheet helpdesk sent me a response this weekend and it makes sense. I'll work on it later this afternoon and post both the advice and results.

Sandi Joralemon

Hello again @KDM,

The solution provided by Smartsheet worked. I am now working through how to maintain the daily counts and KPIs so we can analyze the results to determine which days, weeks, months have the highest customer count by reason for visit.

I really appreciate your help! Thank you.

See the comments from Smartsheet below. I'm not sure how their screen shots are going to show up here. The first picture should be the helper row. The second should be the sheet summary formula.

In reviewing the formula you have provided which I really appreciate, it will return #Unparseable error as expected due to the syntax being used.

The formula =COUNTIF([Arrival Time]:[Arrival Time],WEEKNUMBER([Arrival Time]@row) = WEEKNUMBER(TODAY()))

is trying to count the value in the Arrival Time column which consists of date + time value while the criterion is referencing the weeknumber value (numeric value) of the arrival time column. Countif function is executed to count the number of cells within a range that meets a specified criterion. The range to count and the criterion in the formula does not match that prevents the formula from being interpreted.

As an alternative solution, as provided by the previous support we need to use a helper column to obtain the numeric value of the week, month, and year. Then we will use that helper column as the range to count which validates the criterion of the numeric value of today's week, month, and year.

 

·        =WEEKNUMBER([Arrival Time]@row)

·        =MONTH([Arrival Time]@row)

·        =YEAR([Arrival Time]@row)

Sample:

To get the total count dynamically we will not incorporate the Today() function in the sheet summary fields and we will use the formula: 

 

• =COUNTIF([Weeknumber Helper]:[Weeknumber Helper], WEEKNUMBER(TODAY()))
• =COUNTIF([Month Helper]:[Month Helper], MONTH(TODAY()))
• =COUNTIF([Year Helper]:[Year Helper], YEAR(TODAY()))

Sample

By using the above formula, you are not required to change the week, month, or year number as it will return an expected result based on today's date.

Sandi Joralemon

Smartsheet response screenshot for Helper Row

Smartsheet response for Summary Formula

Sandi Joralemon

@KDM,

I apologize, I didn't see your comment on 6/23. Here are the formulas I ended up using: =COUNTIF(Day:Day, WEEKDAY(TODAY()))

=COUNTIF(Week:Week, WEEKNUMBER(TODAY()))

=COUNTIF(Month:Month, MONTH(TODAY()))

=COUNTIF(Year:Year, YEAR(TODAY()))

The formulas work, but I still haven't figured out how to set up a report that will create an ongoing summary that can be referred back to at any time for historical purposes. I will thinking it through.

Kelly Moore

Hey @Sandi Joralemon

Thinking about your need for an ongoing summary, I wondered if your smartsheet subscription gave you access to Smartsheet's premier app, Pivot app? If your license is an enterprise license then yes, it is included. If it is included, it should be straight-forward to produce a report that gives you the information.

If Pivot app isn't included, one approach could be to build a separate metric sheet with all the month numbers and week numbers running down the grid in one column, or perhaps two columns. You could then, via cross reference formulas, pull in the count-totals for this year, and if you continued adding new columns, collect data for each year to come. In the end, how you want to display and/or report the data potentially could impact if the metrics sheet could work for you.

If this is something that interests you and you need help, shout out.

Kelly

Sandi Joralemon

Hello @KDM,

We have a business account rather than the enterprise account, so unfortunately, I don't have access to the Pivot app. You metric approach is exactly what I was thinking about doing. I will be working on it as able over the next couple of weeks. I have played around with it a little but have not been successful yet. I appreciate the offer of help very much and will more than likely be reaching out to you. Either way, I will update you with the results. Thank you very much!

Kelly Moore

Hey @Sandi Joralemon

I saw this post come through today. In addition to what we discussed above, you may want to monitor this post as a possible approach for you.

https://community.smartsheet.com/discussion/80993/mimic-a-pivot-table-group-by-date-and-sum

cheers,

Kelly

Sandi Joralemon

Great find @KDM. I will keep an eye on it. Thank you!