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

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?

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

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

@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

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)

All comments

Heather Duff

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_

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

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

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_

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

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_

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_

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

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

Paul Newcome

=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

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

Happy holidays!

Kate_

Thank you both so much @Paul Newcome @Heather Duff

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.