+ Reply to Thread
Results 1 to 5 of 5

SUMIFS with multiple columns for current week

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Fort Madison, Iowa
    MS-Off Ver
    2010
    Posts
    2

    SUMIFS with multiple columns for current week

    Hello everyone. First time posting. Actually first day being a member.

    This might have been asked before. Point me in the right direction if so. I have a spreadsheet that we keep track of one of our employees vacation, overtime and attendance. I attached the file. I am trying to track overtime for both current week and last week. The current week overtime is in cell N5 and previous week is in cell N4. The formula in cell N5 works if I only do January. But when I try to include more than one month it gives an error. If the supervisor puts in "OT" in the code field, it will add it to the overtime. Am I over thinking this? Is there a better way?

    Thanks.
    Vince
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    3,561

    Re: SUMIFS with multiple columns for current week

    You can use an array formula - enter this into N5 using Ctrl-Shift-Enter instead of just enter:

    =SUM(IF(C27:BG57="ot",IF(B27:BF57>=N6,IF(B27:BF57<N6+7,D27:BH57))))

    I would also suggest that you do a global spell check - Absense is actually Absence, and so on.... It never looks good to have typos and spelling errors in a work document.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: SUMIFS with multiple columns for current week

    Hi!

    You could try:
    [N5] =SUMIFS(D27:AV57,C27:AU57,"OT",B27:AT57,">="&N6,B27:AT57,"<"&N6+7)
    Blessings!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    3,561

    Re: SUMIFS with multiple columns for current week

    Much better than mine - I did that and it gave me an error - but I must have had an unbalanced range when I thought I didn't - -head smack-

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    Fort Madison, Iowa
    MS-Off Ver
    2010
    Posts
    2

    Re: SUMIFS with multiple columns for current week

    Wow....Thanks guys. That was fast. I was hoping I had a hard one. I tried both of them. I guess I never realized you can select the whole area and still get what I need.

    Thanks....Much appreciated.

+ 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