+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT from using Criteria Arrays

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    SUMPRODUCT from using Criteria Arrays

    Hi - any help on this would be much appreciated.

    Ultimate goal: I have a company producing detailed results by cost center, by site. I wish to create a consolidated result, which is sorted by cost type, by division. I wish to create this so it is user-friendly in the future, so a user can simply add a tab, paste the detailed spread in, and then the worksheet automatically consolidates the results based on pre-determined mapping.

    Eg.
    Tab 1 = Detailed Result ("PL_May_2013")

    A B C D E F
    1 Costs Site1 Site2 Site3 Site4 Site5
    2 Cost1 5 6 2 3 2
    3 Cost2 7 8 2 3 5
    4 Cost3 7 8 2 3 5
    5 Cost4 7 8 2 3 5

    Tab 2 = Costs by Type Mapping ("GL")
    A B
    1 Costs Type
    2 Cost1 Type1
    3 Cost2 Type1
    4 Cost3 Type2
    5 Cost4 Type3

    Tab 3 = Sites by Division Mapping ("Divisions")
    A B
    1 Site Division
    2 Site1 D1
    3 Site2 D1
    4 Site3 D2
    5 Site4 D3

    Finally, Tab 4 = Results ("Active Worksheet")
    A B C D
    1 Type D1 D2 D3
    2 Type1 21 4 6
    3 Type2 15 2 3
    4 Type3 15 2 3

    I can make this work quite well with the following array formula;
    =SUMPRODUCT(--ISNUMBER(MATCH(PL_May_2013!B9:B425,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0))*(--ISNUMBER(MATCH(PL_May_2013!$G$2:$BD$2,IF(Divisions!A2:A50="Plastics Group ",Divisions!C2:C50,0),0)))*PL_May_2013!G9:BD425)

    The issue is that the detailed result shows every cost as a positive number, whereas some are actually negatives. To counter this, in the "GL" mapping tab, I have added an additional column which denotes the cost as a "+1" or "-1", intention being to add this as a final criteria to the SUMPRODUCT. I can't get this formula to work. I have tried adding the following to my formula:

    =SUMPRODUCT(--ISNUMBER(MATCH(PL_May_2013!B10:B426,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0))*IFERROR(OFFSET(GL!D1,MATCH(PL_May_2013!B10:B426,IF(GL!$C$2:$C$880=C9,GL!$D$2:$D$880,0),0),2),0)*(--ISNUMBER(MATCH(PL_May_2013!$G$2:$BD$2,IF(Divisions!A3:A51="Plastics Group ",Divisions!C3:C51,0),0)))*PL_May_2013!G10:BD426)

    However, the "IFERROR" seems to convert the entire array to zeros, eg. {0;0;0;0;0;0;0} and so forth, even though the OFFSET shows [-1, 1, -1, 1, 1, N/A, N/A, etc.}

    Understand this is highly complex - any thoughts would be very much appreciated. Understand it would be easier for me to post the spreadsheet, but I'd prefer not to given its sensitive info.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: SUMPRODUCT from using Criteria Arrays

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    04-12-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: SUMPRODUCT from using Criteria Arrays

    Okay - sample spreadsheet attached.

    My formulas are in the tab "Summary", the function that isn't working is cell C9 - the cell above (C8) works in that it summarises the total "Revenue" for "Blue Group. However, all of the numbers being pulled are positives - I want to denote some of them as negatives -1 and some as 0.5 (Refer tab "GL", column F)

    The data is in the tab "PL_May_2013". All other tabs are for mapping references.

    Please let me know any other information / way I can help - hopefully this makes sense now.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-12-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: SUMPRODUCT from using Criteria Arrays

    Anyone with any ideas on this?

+ 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