I knew the problem was within the ranges. That is always the culprit. You
just have to find it.

"Gladiator" wrote:

Ayo,
I found the error: one cell in the range had "..." in white font and when I
removed that the SUMPRODUCT in all cells started woking fine. Thanks for your
support thoug.

"Gladiator" wrote:

still not working, but I noticed one thing: when I retype the formula it
works fine, but when I lock the ranges it shows '#VALUE!' error and even if I
remove the locks it still shows the error.

"Ayo" wrote:

Try this !

=SUMPRODUCT(('BAC - ABC'!B$5:B$711=Comparison!B13)*('BAC -
ABC'!C$5:C$711=Comparison!C13)*('BAC - ABC'!F$5:F$711))




"Gladiator" wrote:

I did not find '#VALUE!' error in the ranges.

"Ayo" wrote:

Look in your ranges for "#VALUE!" if there is one of this in the range you
are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to
me many times.

"Gladiator" wrote:

Hi all,
This formula sometimes works and sometimes shows #VALUE! error. Please
anyone advice. Thanks.