sumproduct?
 

I am trying to take a set of data and make it simpler to work with, but don't
want to have to retype all the data. On sheet 1(data), I have names in column
A, orders or sales in column b, the day of the week in column c, and the
beginning date of the week in D through BC (not my design). The columns with
the week begin date have the number of orders or sales contained within. What
I would like to get is a second worksheet(records), with 5 columns, names,
week begin date, day of week, orders, and sales. I tried to use sumproduct,
but get an #N/A error.

I used
=SUMPRODUCT((data!$A$2:$A$289=records!$A2)*(data!$ C$2:$C$290=records!$C2)*(data!$D$1:$BC$1=records!$ B2)*(data!$B$2:$B$289=records!$D$1)).
This was to get the orders. Can you tell me where I went wrong, or if I'm
using the wrong function?

Thanks in advance for any help that you can provide.

I caught the error regarding column c, and changed that to $C$289, but now I
get "1" as the result.

"cursednomore" wrote:

I am trying to take a set of data and make it simpler to work with, but don't
want to have to retype all the data. On sheet 1(data), I have names in column
A, orders or sales in column b, the day of the week in column c, and the
beginning date of the week in D through BC (not my design). The columns with
the week begin date have the number of orders or sales contained within. What
I would like to get is a second worksheet(records), with 5 columns, names,
week begin date, day of week, orders, and sales. I tried to use sumproduct,
but get an #N/A error.

I used
=SUMPRODUCT((data!$A$2:$A$289=records!$A2)*(data!$ C$2:$C$290=records!$C2)*(data!$D$1:$BC$1=records!$ B2)*(data!$B$2:$B$289=records!$D$1)).
This was to get the orders. Can you tell me where I went wrong, or if I'm
using the wrong function?

Thanks in advance for any help that you can provide.

By using ranges of different dimensions (e.g., A2:A289, C2:C290), the
product of the array will contain #N/A (e.g., the 289th element for the
two ranges above). This is passed through to SUMPRODUCT's result.

As a first step, try changing C2:C290 to C2:C289.

In article ,
cursednomore wrote:

I used
=SUMPRODUCT((data!$A$2:$A$289=records!$A2)*(data!$ C$2:$C$290=records!$
C2)*(dat a!$D$1:$BC$1=records!$B2)*(data!$B$2:$B$289=record s!$D$1)).
This was to get the orders. Can you tell me where I went wrong, or if
I'm using the wrong function?