# RYG green balls counting weekly

Options

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.

Marat

• ✭✭✭✭✭✭
edited 03/09/21
Options

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,

• Options

Hi Mike,

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Let us know if Bassam's formula worked.

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

Hi Heather,

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

Hi guys,

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

Thank you

• ✭✭✭✭✭✭
Options

I don't see a screenshot

@Marat Nauanov

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!