Sumproduct #value!

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
 
J

JE McGimpsey

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
 
D

Dave Peterson

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...
 
G

Guest

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...
 
B

bplumhoff

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
 
J

JE McGimpsey

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.
 
G

Guest

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...
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Sumproduct? 3
sumproduct with multiple variables 8
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
Sumproduct using Tables 6
Sumproduct Help! 2
sumproduct with varying column ranges 1
Expanded sumproduct 11

Top