RYG green balls counting weekly

Marat Nauanov

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

Screenshot_2021-03-09 MARAT - Smartsheet com.png

Mike Wilday

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,

Marat Nauanov

Hi Mike,

I have only one date column and I want to clarify that I do mean last week, not the last seven days.

Bassam Khalil

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

Mike Wilday

Let us know if Bassam's formula worked.

Marat Nauanov

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

Heather Duff

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)))),"")

Marat Nauanov

Hi Heather,

I used your formula. Now the sell is blank - no numbers and no text in it :(

Bassam Khalil

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.

Heather Duff

@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.

Marat Nauanov

Hi guys,

Yes, Start time set as a date column. I attached smartsheet screenshot.

Thank you

Mike Wilday

I don't see a screenshot

@Marat Nauanov

Marat Nauanov

Oh, sorry. I missed to attach. Now it is here.

Screenshot_2021-03-16 MARAT - Smartsheet com.png