Count if checkbox is checked in each week of the year.
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?
Best Answers
-
@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).
-
@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")
-
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
-
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
-
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.
-
@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).
-
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.
-
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 🤔
-
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)
-
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!
-
@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")
-
@Kate_ Happy to help! Glad you got it to work.
-
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)
-
-
Thank you both so much @Paul Newcome @Heather Duff
Help Article Resources
Categories
Check out the Formula Handbook template!