+ Reply to Thread
Results 1 to 6 of 6

Getting confused with rows and formulas

  1. #1
    Registered User
    Join Date
    03-17-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Getting confused with rows and formulas

    Hi Guys,

    I am banging my head on my keyboard and would love to have a doc help out.

    In a roster, I'd like to figure out how to count all public holidays worked / non-worked for the staff. Ideally I'd love a short and easy formula that my brain could process

    The formula would look at the date in the roster and see if there is a match in a corresponding public holiday table, if there is it will then look at the corresponding day for the employee, if he worked or not and sum all up.

    I have a hard time trying to explain this one, might need to stop looking at it. I tried Index, Lookup, Match and I think I am starting to mix them all up... not ideal.

    I have attached the excel sample with the result I'm expecting.

    Would love to see if an Excel expert has a minute to share.

    Thanks,
    Fab
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Getting confused with rows and formulas

    what does the X mean ?
    what does rest mean ?

    this should work - entered as an array
    using control +shift +enter
    =SUM(COUNTIFS($B$1:$F$1,PH!A$2:A$3,B2:F2,{"Day","Night"}))
    to get
    {=SUM(COUNTIFS($B$1:$F$1,PH!A$2:A$3,B2:F2,{"Day","Night"}))}


    Assuming the X means a rest
    =SUM(COUNTIFS($B$1:$F$1,PH!A$2:A$3,B2:F2,"X"))
    using control +shift +enter
    {=SUM(COUNTIFS($B$1:$F$1,PH!A$2:A$3,B2:F2,"X"))}
    Last edited by etaf; 03-21-2016 at 11:21 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Getting confused with rows and formulas

    You can use this array* formula in G2:

    =SUM(IF((ISNUMBER(MATCH($B$1:$F$1,PH!$A$2:$A$3,0)))*(Roster!$B2:$F2< >"X"),1,0))

    and this array* formula in H2:

    =SUM(IF((ISNUMBER(MATCH($B$1:$F$1,PH!$A$2:$A$3,0)))*(Roster!$B2:$F2="X"),1,0))

    (spot the difference !!), then copy down.

    *Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter>.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    03-17-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    9

    Re: Getting confused with rows and formulas

    Hi Wayne, Pete,

    Thanks a lot for your help. Both works well I love it.

    Really appreciate your input here,
    Fab

    PS: X meant Rest/Rest Day. Sorry I missed that part.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Getting confused with rows and formulas

    Glad to be able to help - thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Pete

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Getting confused with rows and formulas

    you are welcome
    thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Trying to copy formulas but confused with RELATIVE AND ABSOLUTE !
    By mtouhig in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2014, 02:13 PM
  2. Nesting Excel formulas- VLOOKUP, IF,MATCH = confused
    By dylan619xf in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-17-2012, 12:05 AM
  3. Replies: 1
    Last Post: 10-03-2012, 10:32 PM
  4. [SOLVED] Macro executes but not giving results :confused::confused::confused:
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 10:49 AM
  5. Very very confused with searching for rows
    By Flyersfan0044 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2007, 09:54 PM
  6. Confused on lookup formulas. Any Help?
    By Aranon in forum Excel General
    Replies: 11
    Last Post: 11-28-2006, 12:29 AM
  7. [SOLVED] confused on two formulas
    By LInda in forum Excel General
    Replies: 3
    Last Post: 03-30-2006, 04:00 PM
  8. Replies: 1
    Last Post: 03-22-2006, 06:45 PM

Tags for this Thread

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