+ Reply to Thread
Results 1 to 4 of 4

Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

  1. #1
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Exclamation Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

    Afternoon everyone,

    Y'all have been a great deal of help to me and the ongoing project I have been working on so I appreciate any time you have to review my inquiry... I have created a template where I am keeping track of incoming volume, volume in queue, volume completed, volume incomplete, and volume rolled over (second tab of attached workbook).

    I am not trying to change the format of the template (i.e. it needs to remain horizontal). What I am trying to do is capture data that is outside of my expectation of completed work, which is 1, 7 and 30 business days.

    My current formula (WOH!column J) calculates any value in the INCOMP columns of the Completed Work tab if that INCOMP value is present on any day prior TODAY(). When I replace the &TODAY()-1 with &TODAY()-7 or &TODAY()-30, it still works as I expect it to.

    Please Login or Register  to view this content.
    When I reformatted my template to account for rollover work, I was provided the following formula to capture any work that was incomplete and rolled over to the next business day when the Rollover Section for that day is marked as "No" as well.

    Please Login or Register  to view this content.
    This works beautifully, except for the fact that I need to be able to use the formula based on a date because I have different expectations. For this example, I am using 1 business day, when I need to format the formula so that it could also take into account rollover work that have not been completed greater than 7 or 30 days ago.

    I have tried adjusting my current formula to be written similarly to the one provided, but I get an error. What am I doing wrong?

    Please Login or Register  to view this content.
    WORK.xlsx

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

    When I changed the drop downs to "No" the SUMPRODUCT formula you inherited works fine. On the other hand if you change the formula to "Yes" then change the drop down selection(s) accordingly. Other than that you have only left out some parentheses in the SUMPRODUCT adjustments you made.

    I didn't painstakingly check the last SUMIFS formula, but it appears to be the same story RE: drop downs and dates.

    You are getting zeros for TODAY()-7 and TODAY()-30 because the calendar in Completed Work does not go back that far yet. (1/30/2016). Today is 2/4/2016.

    Your adjusted SUMPRODUCT formulae:

    =SUMPRODUCT(('Completed Work'!$B$2:$EZ$2<=TODAY()-1)*('Completed Work'!$B$3:$EZ$3="Rollover")*('Completed Work'!G14:FE14="No")*('Completed Work'!G4:FE4))

    =SUMPRODUCT(('Completed Work'!$B$2:$EZ$2<=TODAY()-7)*('Completed Work'!$B$3:$EZ$3="Rollover")*('Completed Work'!G14:FE14="No")*('Completed Work'!G4:FE4))

    =SUMPRODUCT(('Completed Work'!$B$2:$EZ$2<=TODAY()-30)*('Completed Work'!$B$3:$EZ$3="Rollover")*('Completed Work'!G14:FE14="No")*('Completed Work'!G4:FE4))

    all work fine at my end.
    Last edited by FlameRetired; 02-04-2016 at 09:35 PM. Reason: typos / details / afterthoughts
    Dave

  3. #3
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

    I had a minor typo when testing the formula, thank you for pointing it out! I appreciate your time.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,336

    Re: Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria

    You are welcome. Thank you for the feedback and 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] Sumif or Sumproduct with multiple criteria
    By BPSJACK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-30-2015, 06:25 AM
  2. [SOLVED] index match and sumproduct or sumif on multiple criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 06:20 AM
  3. Sumif or sumproduct with multiple criteria
    By msoregon91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 03:56 PM
  4. [SOLVED] Sumif Formula or Sumproduct Formula? Three Criteria.
    By oHUTCHYo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 10:50 AM
  5. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  6. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM
  7. SUMIF Multiple Criteria or SUMPRODUCT?
    By gdwright07 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2008, 12:15 PM

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