Countifs function to count ratings by location within the past 7 days

I am trying to create a countifs function to count 5-star ratings by restaurant location published within the past 7 days. The published column contains dates. So far, I have this equation:

=COUNTIFS(Rating:Rating, =5, Location:Location, "Bayshore", Published:Published,AND(@cell <= TODAY(), @cell > TODAY(-7))‌

I am getting an #unparseable error message. What am I doing wrong?

Thanks!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi Bart,

There are a few ways to structure the formula. Here's one.

Try something like this.

=COUNTIFS(Rating:Rating; "Five"; Location:Location; "Bayshore"; Published:Published; <=TODAY(); Published:Published; >=TODAY(-7))

The same version but with the below changes for your and others convenience.

=COUNTIFS(Rating:Rating, "Five", Location:Location, "Bayshore", Published:Published, <=TODAY(), Published:Published, >=TODAY(-7))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sign in to join the conversation!

Quick Links

Trending discussions

How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?