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