+ Reply to Thread
Results 1 to 5 of 5

Sumif with cell reference as criteria

  1. #1
    Registered User
    Join Date
    04-07-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Sumif with cell reference as criteria

    Hello,

    I have a report that tracks number of calls that we receive in 15 minute intervals during an entire week. Originally, all of my summary formulas were simple sums that I updated daily. Now, however, I am trying to automate it as much as possible to be able to update it much faster. (A simplified version of the report is attached.)

    I ran into a problem when I go to cell J5. Originally, the formula that I tried was:

    =sumif($B$214:$V$214,"Mon","Tue","Wed","Thu","Fri",$B248:$P291)

    Obviously this didn't work, and I'm not even sure if it makes sense... Essentially, what I am trying to find is this:

    When B214 through V214 is Mon, I need the total number of Received (B248:B291), same for Tue through Fri. On Sat and Sun, I need Q and T, respectively, 256-287.
    The major problem that I found was that in order to automate this report as much as possible, each cell is linked to another, there are other sheets involved on the full report as well. Since the "Days" in B214-V214 are actually formatted dates and not just string days, I'm not sure how to set the criteria to find the DAY as opposed to the DATE. So although B214 is Mon, it is actually March 1, but I need the formula to look for Mon, not March 1.

    Any help will be greatly appreciated :-)

    Sonya
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Sumif with cell reference as criteria

    Hello sonyap,

    I'm not sure I understand. Will J5 not always sum column B? What reason would there be for J5 to look at another column than B?

    By the way, even if you format a date to show as "Mon", the underlying value is still the date, so you would still search for or compare with the date, not the "Mon".

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Sumif with cell reference as criteria

    I agree with Teylyn. You'd need to work in the Weekday function, such as
    =sumproduct((Weekday($B$214:$V$214)<>{7,1})*($B248:$P291)). I haven't looked at the file yet, but hopefully this gives you a head start on working in Weekday to solving your issue.

  4. #4
    Registered User
    Join Date
    04-07-2009
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Sumif with cell reference as criteria

    Hi teylyn and darkyam -

    We need a formula that will calculate the sum for each day of the week because as the month changes, the 1st will not be a Monday.. This report always starts on the First of the month, but we need it to still calculate the sum differently for MON-FRI and SAT-SUN. So in other words, in April, when 4/1 is a Thursday, the simple sum will still work for 4/1 and 4/2, but since 4/1 is a Sat, we will have to manually change it to reflect the SAT-SUN cells.

    Darkyam, I played around a bit with the weekday() function, but didn't get very far. I tried this, but it didn't work:

    =IF(WEEKDAY($B214)=(WEEKDAY($J3)),$B248:$B291,(IF(WEEKDAY($E214)=(WEEKDAY($K3)),$E248:$E291,IF(WEEKDAY($H214)=(WEEKDAY($L3)),$H248:$H291,IF(WEEKDAY($K214)=(WEEKDAY($M3)),$K248:$K291,IF(WEEKDAY($N214)=(WEEKDAY($N3)),$N248:$N291))))))

    Thanks again for all your help!!

  5. #5
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Sumif with cell reference as criteria

    OK, I'm still a bit confused. If B214=J3, then naturally E214=K3, H214=L3, etc. Also, if J3 is going to be the first of the month, and so is B9, then all of these tests are redundant. How exactly will the layout look for April? Will it be any different from March? Is there a case in which my above assumptions are untrue?

    It should be fairly easy to build a sum formula with Index/Match that meets your requirements if you unmerge rows 9, 113, and 214 and then go to Format Cells -> Alignment -> select Center Across Selection under the Horizontal dropdown menu, but first I need to be clear what will be different for April. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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