error using sumproduct

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

I have a sheet named data. On another sheet I am trying
to so sums. The object is to move away from first
concatenate and then sumif and use a single sumproduct
formula.
The formula =sumif(Data!D:D,"oct",Data!E:E) returns a
correct numeric value
The formula =sumif(Data!C:C,"budget",Data!E:E) returns a
correct numeric value
Now I would like to combine the two to find the numeric
value for all occurrences for oct budget.

=sumproduct((Data!D:D="oct")*(Data!C:C="budget")*(Data!
E:E))
This returns #NUM!. I don't understand since the two
parts work correctly.

Any help is greatly appreciated.
 
You cannot use D:D with sumproduct. Try D1:D1000 instead. Or use defined
names where the ranges are the same.
D1:D1000 and E1:E1000
 
Cool, however, over the course of the next twelve months
I am going to have thousands of lines worth of
information. By using the D:D syntax I don't have to
worry about the range being large enough to hold all the
data. When I specify the range with a name I notice a
major lag in data calculation (the hardware is not the
issue).

Is there another way to accomplish what I want to do?
 
Use a defined name for each range. Just make sure that they are the same
length.
insert>name>define>select a name like rngB>in the refers to box type
=offset($B$1,0,0,counta($B:$B),0) and touch enter
now for rngC use
=offset($c$1,0,0,counta($B:$B),0) and touch enter
Notice that I still used B for the count. Use the longest.

This should be faster than calculating 65536 rows
 
You can use dynamic ranges and name them

=OFFSET('Data'!$A$2,,,COUNTA('Data'!$A:$A),1)

the above would be for A in the Data sheet starting with A2,
You can do the same for the other ranges and then just do

=SUMPRODUCT((MyRange1="abcd") and so on

To name it, do insert>name>define and type a descriptive name and in the
refers to box put
a similar formula to the above, note the absolute references which is
important
 
Bryan said:
Cool, however, over the course of the next twelve months
I am going to have thousands of lines worth of
information. By using the D:D syntax I don't have to
worry about the range being large enough to hold all the
data. When I specify the range with a name I notice a
major lag in data calculation (the hardware is not the
issue).

Is there another way to accomplish what I want to do?
Click to expand...
....

Since Excel can't use entire columns with SUMPRODUCT, even if you had all of
column D filled with data, neither D:D nor OFFSET(D1,0,0,65536,1) would work
as arguments to SUMPRODUCT. So you'll never be able to get SUMPRODUCT to
calculate all 65536 cells in any column. However, it happily calculates
65535 rows of data, so D$1:D$65535 would work.
 
Back
Top