# SUMPRODUCT criteria from another workbook

1. ## 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. ## 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.

3. ## 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?

4. ## 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. ## 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. ## 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. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)