+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

pstewart Need help with creating a... 08-10-2012, 11:03 AM
jeffreybrown Re: Need help with creating a... 08-10-2012, 11:11 AM
pstewart Re: Need help with creating a... 08-10-2012, 12:35 PM
jeffreybrown Re: Need help with creating a... 08-11-2012, 09:57 AM
  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    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?
    Attached Files Attached Files
    Last edited by pstewart; 08-10-2012 at 12:55 PM. Reason: changing the attachment

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Need help with creating a formula that counts checkboxes based on criteria within a ra

    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.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need help with creating a formula that counts checkboxes based on criteria within a ra

    ,

    I see how this would work but I have a few issues that I still need to resolve.

    1st issue: The formulas won't track my patients by week or account for having more than one patient a day.

    2nd issue: Is there a simple way to add the cell links all at one time. I will be putting 12 sheets in this workbook (1 for each month of the year).

    Yes, I would like you to explain the benefits of the double-click method and how to set it up.

    Also, how do I re-attach my spreadsheet to this thread? I've made some alterations to it, added the cell links, and added some comments to help illustrate my issues.

    Thanks, so far this has been very enlighting.

    Patrick

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Need help with creating a formula that counts checkboxes based on criteria within a ra

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1