Need help with creating a formula that counts checkboxes based on criteria within a range

Attachment 173593My name is Patrick and I'm new to the forum. My excel knowledge is limited so please be specific. I think I have attached my spreadsheet to this thread for visual purposes but I'm not sure. If not, could someone please explain how to do it and I will give it another try.

QUESTION: I would like to create formulas in the range N7:Q9 that will count the checked checkboxes in columns c, d, e, and f on a weekly basis. Can this be done?

Hi Patrick & Welcome to the Forum,

When using a check box you can assign a cell link

Right click the check box (In C6) >> Format Control >> Cell Link: $S$6

In this case I used S6 and when clicked, S6 will show TRUE. Unclick, FALSE

Now you can use =COUNTIF(S6:S15,TRUE)

In column C you will have to click on each and every check box and take the steps above

Does this help?

Personally, I would use a double click method to simulate a check box and if interested let me know.

Hi Patrick,

Hopefully this is a start and with the Double Click method included.

The spreadsheet is setup dynamically in regards to the months and years.

In D2, select the Year which will control the months in F2. (Named ranges in column AA and AB)

In Column L, added a helper column to identify the weeknum which is set for a Monday start to the week. If you don't want this column to be seen on the spreadsheet you could change the font to white but make sure it doesn't get deleted.

WEEKNUM(serial_num,return_type)

Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines on which day the week begins. The default is 1.

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

I'm kind of making an assumption to your requirements towards 1st Wk, 2nd Wk, etc, but some months could have 5 weeks so I adjusted for that in N:R. Not sure if this is what you desire.

As you have a patient for any day and any category, just double click in that cell and you'll get a check mark, but if you double click again the check will go away.

This workbook is now saved as a .xlsm because it requires a macro to create the check mark.

Right click on the sheet tab and view code. The range for check marks is set as a named range in the name manager (Ctrl + F3).

In N6:R9 there is a countifs function with the weeknum for that week in N4:R4 which should also not be deleted.

Now you can just copy the Aug tab and then change the month in F2 to Sep and so on.

I may have overlooked something, but if so, please let me know and we can hopefully get it straightened out.