+ Reply to Thread
Results 1 to 2 of 2

Iferror(sumproduct) to sort by due date

  1. #1
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    196

    Iferror(sumproduct) to sort by due date

    Please refer to the attached file.
    I'm trying to figure out a way to track the number of days that a work order was in queue, see archive column M, but only counting those work orders that had a due date (see archive column H)
    On the tab Nov Stats column E row 5 I count the average lead time for closed orders which would be the average number of days in queue before closing the order, but I'm not sure how to get it to count only items that had a due date.

    Thanks in advance for the help
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Iferror(sumproduct) to sort by due date

    I think you are close to the correct formula there, but first, let me introduce you to Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel. There are a lot of advantages to tables in Excel. The two that really count here are:

    1. Tables know how big they are, so you don't have to guess how big your formulas have to be. I notice that you reference about 4600 rows even though you barely have a quarter of that. By referencing the table, the table will reference the 1083 rows you have. When you add row 1084, the table will grow to accommodate it.

    2. You can use the table header names in formulas. You don't have to remember that column K is Code and Column F is Date Closed.

    Tables also copy down formulas, formats, validations ... automatically, and they are just as easy to reference in VB.

    So your new formula looks like:=IFERROR(SUMPRODUCT(--(Table1[Code]="P/S"),--(MONTH(Table1[Date Closed])>=1),Table1[Workdays in queue])/SUMPRODUCT(--(Table1[Code]="P/S"),--(MONTH(Table1[Date Closed])>=1),--(Table1[Date Closed]<>"")),0)

    All I did was throw in one more condition: --(Table1[Date Closed]<>"")

    And if you change the name of the table from Table1, the formulas will catch it.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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] Iferror in date cell
    By davidpierce in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2015, 04:15 AM
  2. [SOLVED] sumproduct to sort by year to date
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-29-2014, 10:17 AM
  3. [SOLVED] =IFERROR / SUMPRODUCT - Search for year total?
    By domgilberto in forum Excel General
    Replies: 5
    Last Post: 05-20-2014, 05:56 AM
  4. [SOLVED] IFERROR Statement in VBA for date
    By Maxy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2013, 08:44 AM
  5. IFERROR and DATE
    By Kevalin O in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2013, 05:24 AM
  6. xlfn.IFERROR or IFERROR don't work in Excel 98-2003
    By dj_danu01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 02:53 AM

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