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



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

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

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

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.

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.

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.

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