Sumproduct #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

Sumproduct #value!
 

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



In article ,
wal50 wrote:

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

Sumproduct #value!
 

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

wal50 wrote:

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

--

Dave Peterson

Sumproduct #value!
 

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" wrote:

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



In article ,
wal50 wrote:

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

Sumproduct #value!
 

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

Sumproduct #value!
 

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.

In article ,
wal50 wrote:

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.

Sumproduct #value!
 

Thanks. Anything to make it easier is good.

"JE McGimpsey" wrote:

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.

In article ,
wal50 wrote:

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.

Sumproduct #value!
 

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

" wrote:

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

Sumproduct #value!
 

Sumproduct works on arrays, but is not entered as an array formula.
--
Kevin Vaughn


"wal50" wrote:

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

" wrote:

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