Sumproduct
 

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:B1)

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

Sumproduct
 

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:B20011))

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"ocuhcs" wrote:

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:B1)

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

Sumproduct
 

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

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

You may have intended
=SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale
Orders'!F2:F2001),--(Invoices!B:B1))
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:B20011)) ?

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

"ocuhcs" wrote in message
...
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:B1)

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

Sumproduct
 

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:F20011

"David Biddulph" wrote:

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

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

You may have intended
=SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale
Orders'!F2:F2001),--(Invoices!B:B1))
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:B20011)) ?

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

"ocuhcs" wrote in message
...
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:B1)

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

Sumproduct
 

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.
--
David Biddulph

"ocuhcs" wrote in message
...
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:F20011

"David Biddulph" wrote:

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

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

You may have intended
=SUMPRODUCT(--(Invoices!F2:F2001=A2),--('Wholesale
Orders'!F2:F2001),--(Invoices!B:B1))
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:B20011)) ?

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

"ocuhcs" wrote in message
...
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:B1)

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

Sumproduct
 

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)

"David Biddulph" wrote:

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.

Sumproduct
 

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

On Jul 30, 2:27*pm, ocuhcs wrote:
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)



"David Biddulph" wrote:
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.- Hide quoted text -

- Show quoted text -

Sumproduct
 

You mustn't have read the part of my earlier message
] (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.
--
David Biddulph

"ocuhcs" wrote in message
...
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)

"David Biddulph" wrote:

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.