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
>