Help With Error In Formula Please

[Joe Gieder]

I have this formula that returns a "#NUM" error and was
hoping someone could help please:

=SUMPRODUCT(--('Priced BOM'!$L:$L="Purchase History"),--
('Priced BOM'!$W:$W<TODAY()-1095),--('Priced BOM'!
$W:$W>=TODAY()-1460))

It counts the number of Purchase History items that are
over 3 years and less than 4 years old.

Thank you for the help and support you all offer people
like me that are eager to learn more and sometimes run
into problems.

Joe

 

[Frank Kabel]

Hi
SUMPRODUCT does not accepts ranges like $L:$L
try the following instead
=SUMPRODUCT(--('Priced BOM'!$L1:$L1000="Purchase History"),--('Priced
BOM'!$W1:$W1000<TODAY()-1095),--('Priced
BOM'!$W1:$W1000>=TODAY()-1460))

 

[Peo Sjoblom]

Change all the cell references to something like $L$1:$L$10000,
$W$1:$W$10000
sumproduct will always throw num errors if you use the whole column

 

[Joe Gieder]

Is there another way to write this formula with using the
complete column?

 

[Peo Sjoblom]

$L$1:$L$65535 uses the whole column but 1