RYG green balls counting weekly
Hello,
Could someone please provide a formula to count Green symbols in STATUS column during the past week? I need weekly report of how many green we get during past week . Also should I create special column for counting or I can use existing STATUS column.
Thanks in advance.
Marat
Answers
-
Would you consider the date to count against the start date? Or do you have another date column?
=countifs(status:status, "Green", [Start Time]:[Start Time], AND(@cell >= Today(-7), @cell <= Today()))
This will count all greens with a start date that is equal to or greater than today - 7 days up to the current day,
-
Hi Mike,
I have only one date column and I want to clarify that I do mean last week, not the last seven days.
-
Hi @Marat Nauanov
Hope you are fine, to add to @Mike Wilday excellent answer which I use as a reference to build my formula to match your criteria. please find in the following your formula:
=COUNTIFS(STATUS:STATUS, "Green", [START TIME]:[START TIME], AND(@cell > TODAY(-(WEEKDAY(@cell) + 7)), @cell < TODAY(-WEEKDAY(@cell))))
the following screenshot shows the result
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Let us know if Bassam's formula worked.
-
Hi Bassam,
I applied formula you created but it did not work. It says: " INVALID OPERATION". I change date format to Feb 20 2021 and renamed column to "Count1" but no change. Also I noticed your formula for last seven days ? But I need for last week.
ThankS,
Marat
-
Hi @Marat Nauanov ,
Try adding an IFERROR to the formula:
=IFERROR(COUNTIFS(STATUS:STATUS, "Green", [START TIME]:[START TIME], AND(@cell > TODAY(-(WEEKDAY(@cell) + 7)), @cell < TODAY(-WEEKDAY(@cell)))),"")
-
Hi Heather,
I used your formula. Now the sell is blank - no numbers and no text in it :(
-
Hi @Marat Nauanov
Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Marat Nauanov Is your Start Time column set (in the column properties) as a date column? If not, I don't think the dates will count properly.
-
Hi guys,
Yes, Start time set as a date column. I attached smartsheet screenshot.
Thank you
-
I don't see a screenshot
-
Oh, sorry. I missed to attach. Now it is here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!