Sumproduct

[ocuhcs]

I have a cost worksheet where I need to calculate a sum based on 2 different
critera that are entered onto 2 seperate worksheets. One worksheet is an
order entry worksheet where an order # and value are entered, the second
worksheet is an invoice worksheet where the invoice # and invoice value are
entered, the order # is also entered here for cross referencing, there is
also a delivery code on each invoice line, the delivery code may apply to
several invoices. What I need is a formula to sum up the total order value on
sheet #1 for corresponding order #'s on sheet #2 that all have matching
delivery codes.

The current formula I am tryin is:

=SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale
Orders'!F2:F2001))--(Invoices!B:B>1)

The result is the total order value for all orders on the order sheet.

 

[M Kan]

3 immediate points. The ranges must be the same. You can't use B:B, it nees
to be B2:B2001 and your arguments should be separated by commas. Also, your
whole sale orders don't need the parens or -- because it isn't a criteria.

=SUMPRODUCT(--(Invoices!F2:F2001=A2),'Wholesale
Orders'!F2:F2001,--(Invoices!B2:B2001>1))

 

[David Biddulph]

I think you may have missed a few commas from your formula.

=SUMPRODUCT(--(Invoices!F2:F2001=A2)--('Wholesale
Orders'!F2:F2001))--(Invoices!B:B>1) is equivalent to
=SUMPRODUCT((Invoices!F2:F2001=A2)+('Wholesale
Orders'!F2:F2001))+(Invoices!B:B>1)

You may have intended
=SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale
Orders'!F2:F2001),--(Invoices!B:B>1))
but in that case you would have tried to ask for a whole column for column B
whereas you've asked for rows 2 to 2001 for column F on the 2 sheets. You
need the same dimensions for all the arrays in your SUMPRODUCT.

Perhaps you may have wanted
=SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale
Orders'!F2:F2001),--(Invoices!B2:B2001>1)) ?

But what are you looking for in your --('Wholesale Orders'!F2:F2001)) test?
Is it just looking for a non-zero value?

 

[ocuhcs]

The column 'Wholesale Orders'!F2:F2001 contains the order value that I want
summed up. I think the trick here that I am missing is that this value is
only summed up when (Invoices!B2:B2000) is equal to 'Wholesale
Orders'!B2:B2000
and Invoices!F2:F2001>1

 

[David Biddulph]

So those are the conditions which you should include in your SUMPRODUCT,
along with your 'Wholesale Orders'!F2:F2001 value (which doesn't need the
double unary minus if you are not trying to convert from Boolean to a
number).

But as we said earlier, your arrays need to be of the same length. You
can't go from 2 to 2001 in some parts and 2 to 2000 in other parts of the
same formula.

 

[ocuhcs]

I tried the following formula that contians all the correct conditions, but
comes up with an incorrect sum as the result.
=SUMPRODUCT(--(Invoices!B2:B2000='Wholesale
Orders'!B2:B2000))--(Invoices!F2:F2000=A2)--('Wholesale Orders'!F2:F2000)

 

[Pete_UK]

You do not have the correct syntax - you've missed a few commas (or
your regional settings might use semi-colons), as well as a bracket at
the end, and you have an extra one in the middle. Try it like this:

=SUMPRODUCT(--(Invoices!B2:B2000='Wholesale Orders'!B2:B2000),--
(Invoices!F2:F2000=A2),--('Wholesale Orders'!F2:F2000))

Hope this helps.

Pete

 

[David Biddulph]

You mustn't have read the part of my earlier message
[news:[email protected]] (the content of which you've
snipped) where I said you seemed to have missed a few commas and got the
syntax wrong (and where I suggested what you might have been trying to do).
And you've also presumably not read the bit below where I said that the
'Wholesale Orders'F... bit doesn't need the double unary minus if you are
not trying to convert from Boolean to a number.