Sumproduct #value!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following function returns the correct count of the items meeting the
conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1)))

The problem occurs when I do the following to obtain the total for records
in column C meeting the same conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1))*(([RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647)))
The response is #VALUE!

Column C is in Number format; both are committed as array. What am I missing?

Thanks for your help.

WAL
 
Your formula works fine, so check for a cell in

[RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647

that contains #VALUE! and is passing it through
 
Check C1:c8647 for non-numeric values.

Maybe you can use:

=counta(c1:c8647)
to get a count of all cells with something in them

=count(c1:c8647)
to get a count of just the numeric data

I think you'll find a difference.

Maybe text, maybe even an error value???
The following function returns the correct count of the items meeting the
conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1)))

The problem occurs when I do the following to obtain the total for records
in column C meeting the same conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1))*(([RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647)))
The response is #VALUE!

Column C is in Number format; both are committed as array. What am I missing?

Thanks for your help.

WAL
Click to expand...
 
The only thing that wasn't a number was the column lable in row 1. When I
made the range C2:C8497, it worked. Thanks for the hint. I guess I should
leave out the label row in the future.

WAL

JE McGimpsey said:
Your formula works fine, so check for a cell in

[RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647

that contains #VALUE! and is passing it through



wal50 said:
The following function returns the correct count of the items meeting the
conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*
([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*
([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1)))

The problem occurs when I do the following to obtain the total for records
in column C meeting the same conditions:
=SUMPRODUCT(([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$2)*
([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,4,1))*
([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(2006,5,1))*(
([RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647)))
The response is #VALUE!

Column C is in Number format; both are committed as array. What am I
missing?

Thanks for your help.

WAL
Click to expand...
Click to expand...
 
Hello,

Don't enter them as array formulas. It is not necessary.

You have a #VALUE! error in range C1:C8647, I presume. Have a look into
these cells and eliminate that error.

Finally I suggest to use
=SUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$­2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,­4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(200­6,5,1)))
to count and
=SUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$­2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,­4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(200­6,5,1)),[RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647)
to sum (NOT array-entered). But: Your original formulas should work
after elimination of the error value(s).

Regards,
Bernd
 
Actually, if you use the "correct" syntax of SUMPRODUCT, you can use
labels just fine, as SUMPRODUCT is set up to disregard anything than's
non-numeric. Instead of multiplying the ranges in the argument (so that
the arrays are multiplied prior to being handed to SUMPRODUCT), enter
them as separate arguments. For instance, instead of

=SUMPRODUCT(a * b * c)

use

=SUMPRODUCT(a, b, c)

for arrays of the form (A1:A1000=5), which return booleans, use double
negation to coerce the boolean into a numeric value:

=SUMPRODUCT(--(a)=0), --(a<=100),c)

See

http://www.mcgimpsey.com/excel/doubleneg.html

for more explanation.

As an added benefit, passing the arrays separately is at least slightly
faster than multiplying them first.
 
Thanks. Anything to make it easier is good.

JE McGimpsey said:
Actually, if you use the "correct" syntax of SUMPRODUCT, you can use
labels just fine, as SUMPRODUCT is set up to disregard anything than's
non-numeric. Instead of multiplying the ranges in the argument (so that
the arrays are multiplied prior to being handed to SUMPRODUCT), enter
them as separate arguments. For instance, instead of

=SUMPRODUCT(a * b * c)

use

=SUMPRODUCT(a, b, c)

for arrays of the form (A1:A1000=5), which return booleans, use double
negation to coerce the boolean into a numeric value:

=SUMPRODUCT(--(a)=0), --(a<=100),c)

See

http://www.mcgimpsey.com/excel/doubleneg.html

for more explanation.

As an added benefit, passing the arrays separately is at least slightly
faster than multiplying them first.
Click to expand...
 
Sumproduct works on arrays, but is not entered as an array formula.
--
Kevin Vaughn


wal50 said:
I thought sumproduct was always entered as an array. Is that wrong?

[email protected] said:
Hello,

Don't enter them as array formulas. It is not necessary.

You have a #VALUE! error in range C1:C8647, I presume. Have a look into
these cells and eliminate that error.

Finally I suggest to use
=SUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$­2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,­4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(200­6,5,1)))
to count and
=SUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$8647=C$­2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=DATE(2006,­4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647<DATE(200­6,5,1)),[RetaileastEfficiency.xls]RetaileastEfficiency!$C$1:$C$8647)
to sum (NOT array-entered). But: Your original formulas should work
after elimination of the error value(s).

Regards,
Bernd
Click to expand...
Click to expand...
 
Back
Top