Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |