SUMPRODUCT is not working
 

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

SUMPRODUCT is not working
 

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.

SUMPRODUCT is not working
 

here is the formula:

=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:

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

SUMPRODUCT is not working
 

Not much detail for us to work with. Some examples of non-working SUMPRODUCT
formulas would have been helpful. My guess? More than likely you have
specified ranges that are different sizes... all ranges (that are to be
iterated over) must contain the same number of cells.

--
Rick (MVP - Excel)



"Gladiator" wrote in message
...
Hi all,
This formula sometimes works and sometimes shows #VALUE! error. Please
anyone advice. Thanks.

SUMPRODUCT is not working
 

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.

SUMPRODUCT is not working
 

You need to check each row from Row 5 to Row 711 in column B to F. Your
problem is withinthat range.

"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.

SUMPRODUCT is not working
 

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.

SUMPRODUCT is not working
 

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.

SUMPRODUCT is not working
 

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.

SUMPRODUCT is not working
 

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.