+ Reply to Thread
Results 1 to 6 of 6

Schedule to count multiple lines and columns to meet two criteria

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Shreveport, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Schedule to count multiple lines and columns to meet two criteria

    Hi all,

    I have been looking all over the site for a solution to this problem. I have excel 2010.

    The page is set up to look like a calendar with each day containing 7 shifts, the last row in each day is to notify if that day is a holiday(which would count as a point and a half). I can't get the formulas to calculate the following count if B11:H11 = 'HOLIDAY' AND B4:H11 = NAME '(ie. D:59)'. Is this possible?
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Schedule to count multiple lines and columns to meet two criteria

    Hi isudecat04,

    welcome to the forum.
    I can't get the formulas to calculate the following count if B11:H11 = 'HOLIDAY' AND B4:H11 = NAME '(ie. D:59)'. Is this possible?
    see attached where I have used below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Schedule.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Shreveport, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Schedule to count multiple lines and columns to meet two criteria

    No, That counted the cells if they equaled holiday only. Not if they equaled holiday and employee.

    This is the only formula that I found worked. It allowed me to determine my two if statements if one or both equals zero it would count that day to be 0.

    =(COUNTIF($B4:$B11,D58)*COUNTIF($B11,$A$66)

    I appreciate the help. If you have something that would simplify this then that could be helpful.

    Thanks,

    G

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Schedule to count multiple lines and columns to meet two criteria

    but this formula is giving as output Zero - is that you want ?
    A cell can either have "Holiday" or "a name" and hence I suggested that - Now I am not sure what you are looking for

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    06-11-2013
    Location
    Shreveport, LA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Schedule to count multiple lines and columns to meet two criteria

    Schedule.xlsx

    Please see attached file. This gives me the result I'm looking for. Since mark isn't on the day marked holiday, he doesn't get counted for working on a holiday. where as steve and john both worked on a holiday and it gave them a result of "1". The main problem(or should I say inconvenience), is that the formula is so long.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Schedule to count multiple lines and columns to meet two criteria

    Okay.... use below formula to shorten the approach & to extract the similar results:-


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also, you'll get a 1 extra because "10TH FLOOR NOTE" is repeated (duplicate)


    Regards,
    DILIPandey
    <click on below * if this helps>

+ 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