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

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 <EE45ED15-14BD-44C2-B1CD-CFAE93134E34@microsoft.com>,
wal50 <wal50@discussions.microsoft.com> 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

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 <EE45ED15-14BD-44C2-B1CD-CFAE93134E34@microsoft.com>,
> wal50 <wal50@discussions.microsoft.com> 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
>

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 <B7005912-5D56-4E6E-BF98-DF2596649E90@microsoft.com>,
wal50 <wal50@discussions.microsoft.com> 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 <B7005912-5D56-4E6E-BF98-DF2596649E90@microsoft.com>,
> wal50 <wal50@discussions.microsoft.com> 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.
>

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

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
=3DSUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$864=
7=3DC$=AD2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=
=3DDATE(2006,=AD4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$=
1:$B$8647<DATE(200=AD6,5,1)))
to count and
=3DSUMPRODUCT(--([RetaileastEfficiency.xls]RetaileastEfficiency!$D$1:$D$864=
7=3DC$=AD2),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$1:$B$8647>=
=3DDATE(2006,=AD4,1)),--([RetaileastEfficiency.xls]RetaileastEfficiency!$B$=
1:$B$8647<DATE(200=AD6,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

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

"bplumhoff@gmail.com" 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?
>
> "bplumhoff@gmail.com" 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
> >
> >