SUMPRODUCT is not working
in [Programming]
|
From: Ayo on 28 Apr 2010 13:31 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.
From: Ayo on 28 Apr 2010 13:34 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.
From: Gladiator on 28 Apr 2010 13:48 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.
From: Gladiator on 28 Apr 2010 13:54 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.
From: Ayo on 28 Apr 2010 14:04 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.
First
|
Prev
|
Pages: 1 2 Prev: Using vba find Next: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!! |