+ Reply to Thread
Results 1 to 7 of 7

SUMPRODUCT criteria from another workbook

  1. #1
    Registered User
    Join Date
    11-12-2014
    Location
    Glos, England
    MS-Off Ver
    MS Office 2007
    Posts
    14

    SUMPRODUCT criteria from another workbook

    Hi All,

    Just joined, been searching topics but could not figure this out. I'm familiar to using SUMIF, COUNTIF etc but recently attempting to use SUMPRODUCT due to its ability to work with closed workbooks.

    I am looking to sum the total of numbers in columns C, D, E providing the corresponding row in column A does not match two possible text strings, for example lets call these " 'R6 - Oil Drill " and " 'Spanner " (another words I want to exclude these two).

    Just to complicate matters slightly, columns C, D, E do often contain blank cells which I understand SUMPRODUCT can sometimes count? (these are the columns were the values are kept). Column A will contain values between A2:A16 that i want to evalute for the criteria.

    Let's call the workbook im writing the formula in Capacity and the workbook with the data i want to reference Volume.

    Thanks in advance for any help

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: SUMPRODUCT criteria from another workbook

    I'd recommend writing the formulas while the workbooks are both open, as Excel will fill all the filepathing junk in for you.

    Let's instead then pretend you're just working with two worksheets in the same book.

    =SUMPRODUCT((Volume!$A$2:$A$16<>" 'R6 - Oil Drill ")*(Volume!$A$2:$A$16<>" 'R6 - Oil Drill ")*(Volume!$C$2:$C$16+Volume!$D$2:$D$16+Volume!$E$2:$E$16))

    When doing this in your application, actually select those ranges in Volume and the Excel magic sparkles will spell out of the required filepath connections for you.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    11-12-2014
    Location
    Glos, England
    MS-Off Ver
    MS Office 2007
    Posts
    14

    Re: SUMPRODUCT criteria from another workbook

    Thanks that works great, updating with my closed workbook, which is just music to my ears. Can't thank you enough! Just for my understanding why is the first part reapeated twice?
    (Volume!$A$2:$A$16<>" 'R6 - Oil Drill ")*(Volume!$A$2:$A$16<>" 'R6 - Oil Drill ")

    Also I am new here, How do I mark this as solved?

    Thanks for your help!

  4. #4
    Registered User
    Join Date
    11-12-2014
    Location
    Glos, England
    MS-Off Ver
    MS Office 2007
    Posts
    14

    Re: SUMPRODUCT criteria from another workbook

    Also one more thing, I want to set up links to workbooks that do not actually exist yet - they are produced daily, I am able to add a simple IF(ISERROR(THEFORMULAHERE),"-",(THEFORMULAHERE)) into it which takes care of the #REF! that displays, which is nice a simple, however naturally excel tries to link to the workbook and then brings the open document screen as it doesnt find it, is there anyway of preventing this?

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: SUMPRODUCT criteria from another workbook

    Let me describe some of how SUMPRODUCT works, which should shed some light on the multiplication of the ranges.

    SUMPRODUCT is the SUM (addition) of PRODUCTS (multiplication).

    We'll use imaginary ranges, A1:A3 = George, Mustang, Daffodil11 and B1:B3 = 1, 2, 3.

    =SUMPRODUCT((A1:A3<>"George")*(A1:A3<>"Daffodil11")*(B1:B3))

    First criteria, (A1:A3 <> "George") evaluates to {False;True;True}
    Second criteria (A1:A3 <> "Daffodil11") evaluates to {True;True;False}

    Now, when we expose True or False to an operator (read: +, -, *, ÷) they are coerced into 1 or 0, respectively.

    =SUM(TRUE;TRUE) means nothing, but TRUE+TRUE = 2. The simple "+" between them changes them into 1's.

    {False;True;True} * {True;True;False} =

    False*True = 0*1 = 0
    True*True = 1*1 = 1
    True*False = 1*0 = 0

    That {0;1;0} is multiplied next times {B1:B3}

    0*1 = 0
    1*2 = 2
    0*3 = 0

    We are left with {0;2;0} and we are done with the multiplication. Now we SUM, and 0+2+0 = 2.

    =SUMPRODUCT((A1:A3<>"George")*(A1:A3<>"Daffodil11")*(B1:B3)) = 2

    Essentially, the * between criteria means "AND" because all must be true to not produce a zero.

  6. #6
    Registered User
    Join Date
    11-12-2014
    Location
    Glos, England
    MS-Off Ver
    MS Office 2007
    Posts
    14

    Re: SUMPRODUCT criteria from another workbook

    I had to read it a couple of times but you explained it well, and opened my eyes to how this is working, thanks again this is really helpful. Much appreciated

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: SUMPRODUCT criteria from another workbook

    Glad I could help out. Once you start to dabble with arrays, it really opens a lot of doors.

+ 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] Multiple criteria SUMPRODUCT (3 criteria) Excel 2003
    By lelrich in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2014, 01:58 PM
  2. Replies: 6
    Last Post: 08-04-2013, 11:53 AM
  3. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  4. Replies: 2
    Last Post: 05-25-2010, 06:55 PM
  5. SUMPRODUCT with Criteria
    By tek9step in forum Excel General
    Replies: 3
    Last Post: 06-04-2009, 08:01 AM

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