Count if checkbox is checked in each week of the year.

Options
Kate_
Kate_ ✭✭✭✭
edited 12/13/21 in Formulas and Functions

Hi

I'd like to count a row if a checkbox is checked within a specific time frame.

e.g. If Column 'Role' is "LMS" and column 'Complete' during a specific time period (e.g. week 6 of the year), count as 1.

=COUNTIF(Complete:Complete, Role:Role, "LMS", Complete:Complete, "1", WeekNumber=6)

I've been playing with various formulaes but I have no idea what I'm doing. Do I need a column with the week numbers in or will the formulae pull this?

Tags:

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    @Kate_ Oh yes, that would make a big difference!

    Try this:

    =COUNTIFS(Complete:Complete, 1, Role:Role, "LMS", Weeknumber:Weeknumber, 6)


    I am assuming that your WeekNumber column already has a formula calculating the week number in it. (Didn't catch that previously).

  • Kate_
    Kate_ ✭✭✭✭
    Answer ✓
    Options

    @Heather Duff thank you so much for your help. I've managed to get it to work. I ended up changing it slightly but you helped me get to this point. I added a date column and a GetWeek column. When the completed box is checked the date stamp is added to the date column using a workflow. The GetWeek column has the following formulae in it =WEEKNUMBER([Completed date]38)

    I then used this formulae in the summary column to count each task.

    =SUMIFS([Amt through stage]:[Amt through stage], Role:Role, "DEV", Completed:Completed, "1", GetWeek:GetWeek, "50")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Apologies for the delayed response. Have been pretty busy lately. @Heather Duff this should answer your question and @Kate_ this will allow you to use only the date column and get rid of the "GetWeek" column...


    =SUMIFS([Amt through stage]:[Amt through stage], Role:Role, @cell = "DEV", Completed:Completed, @cell = 1, [Completed Date]:[Completed Date], IFERROR(WEEKNUMBER(@cell), 0) = 50)

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi @Kate_ ,


    You will want to use an IF/AND formula. Try something like this, being sure the column names match what I have put before each @row:

    =IF(AND(Complete@row = 1, Role@row = "LMS", WEEKNUMBER(weeknumber@row)=6),1,0)

    This translates to:

    If all three of the following criteria (Complete column in this row is checked, Role column in this row is LMS, and the week number in the WeekNumber column of this row is week 6) are true, show the box checked; otherwise, unchecked.


    Hope this helps. Let me know if it works for you!


    Best,

    Heather

  • Kate_
    Kate_ ✭✭✭✭
    Options

    Thanks @Heather Duff I've tried that but it comes up as #UNPARSEABLE I have tried a few changes but no luck. I'm placing it in the sheet summary, would that make a difference? I'm basically trying to count all the checkboxes in a column that were checked within a specific time frame. The aim is to have a sheet summary box for each week of the year and count how many boxes were checked each week.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    @Kate_ Oh yes, that would make a big difference!

    Try this:

    =COUNTIFS(Complete:Complete, 1, Role:Role, "LMS", Weeknumber:Weeknumber, 6)


    I am assuming that your WeekNumber column already has a formula calculating the week number in it. (Didn't catch that previously).

  • Kate_
    Kate_ ✭✭✭✭
    Options

    I don't have a column for weeknumber. I was hoping the syntax would calculate the week number based on the date the user selects the checkbox. I might be wanting to do the impossible :-) We have an agile way of working so boxes could be selected in any row of the sheet and we want to see how many are selected each week.

  • Kate_
    Kate_ ✭✭✭✭
    Options

    I'm wondering if I need a column where a date is populated when the checkbox is checked in order to return a week number 🤔

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    If you already have a date column, the sheet summary formula should be able to calculate the week number.

    @Paul Newcome @Andrée Starå Calling in the experts here, as my brain can't remember. How would one use WEEKNUMBER within a COUNTIFS in a sheet summary? @cell and @row don't seem to be working in my test sheet...

    Looking for a way to replace the criteria in bold below with a WEEKNUMBER([date column])=6 calculation - without using a helper column.

    =COUNTIFS(Complete:Complete, 1, Role:Role, "LMS", Weeknumber:Weeknumber, 6)

  • Kate_
    Kate_ ✭✭✭✭
    Options

    I might have found a way to get the date and the week number by adding two columns and using automations to add the date to the first!

  • Kate_
    Kate_ ✭✭✭✭
    Answer ✓
    Options

    @Heather Duff thank you so much for your help. I've managed to get it to work. I ended up changing it slightly but you helped me get to this point. I added a date column and a GetWeek column. When the completed box is checked the date stamp is added to the date column using a workflow. The GetWeek column has the following formulae in it =WEEKNUMBER([Completed date]38)

    I then used this formulae in the summary column to count each task.

    =SUMIFS([Amt through stage]:[Amt through stage], Role:Role, "DEV", Completed:Completed, "1", GetWeek:GetWeek, "50")

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @Kate_ Happy to help! Glad you got it to work.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Apologies for the delayed response. Have been pretty busy lately. @Heather Duff this should answer your question and @Kate_ this will allow you to use only the date column and get rid of the "GetWeek" column...


    =SUMIFS([Amt through stage]:[Amt through stage], Role:Role, @cell = "DEV", Completed:Completed, @cell = 1, [Completed Date]:[Completed Date], IFERROR(WEEKNUMBER(@cell), 0) = 50)

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @Paul Newcome Thank you! The @cell and IFERROR are what I was missing.


    Happy holidays!

  • Kate_
    Kate_ ✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!