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:


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.


David Biddulph

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

Orders'!F2:F2001))--(Invoices!B:B>1) is equivalent to

You may have intended
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
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?


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
and Invoices!F2:F2001>1


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


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.




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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question