+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT & Dynamic Range Problem

  1. #1
    Registered User
    Join Date
    02-20-2009
    Location
    Mineral, VA
    MS-Off Ver
    Excel 2003
    Posts
    73

    SUMPRODUCT & Dynamic Range Problem

    I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel.

    Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.

    In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:

    Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)
    InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)

    So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))

    His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error. I would REALLY appreciate some help on this one.
    Last edited by cedarhill; 02-22-2009 at 06:21 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUMPRODUCT & Dynamic Range Problem

    If as you say column D on Posting contains blanks then your DNR will not be created properly... COUNT will obviously ignore blanks (it counts Numerics...)

    Note: for purposes of demo I'm assuming your technician values are numeric ids formatted to say 0000 rather than being actual text
    (ie ISNUMBER(technician value) = TRUE)

    Consider

    Please Login or Register  to view this content.
    Then the following DNR's

    Please Login or Register  to view this content.
    will generate a range A1:A5

    Please Login or Register  to view this content.
    will generate a range B1:B4

    The COUNT in the 2nd range (B:B) = 4 thus the range is only 4 rows high...

    In Sumproduct terms having ranges of different dimensions will invariably cause problems.

    If Technician never contains blanks then setup InDate off that range:

    Please Login or Register  to view this content.
    You should then find your DNR will work:

    Please Login or Register  to view this content.
    using sample data the above would return 1
    Last edited by DonkeyOte; 02-22-2009 at 05:49 AM.

  3. #3
    Registered User
    Join Date
    02-20-2009
    Location
    Mineral, VA
    MS-Off Ver
    Excel 2003
    Posts
    73

    Thumbs up Re: SUMPRODUCT & Dynamic Range Problem

    It worked PERFECTLY. Thank you for saving my sanity. (I also got a good lesson in dynamic name range set up that helped me understand them better.)

    Since this is my first ever posting, I've got to figure out how to mark this SOLVED!

    Thanks again!

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SUMPRODUCT & Dynamic Range Problem

    See the How To section in the page links (ie below your thread title)
    EDIT: you found it seemingly...

+ 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