Prev: Using vba find
Next: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!!
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 !!!!!!!!!!!!!!!!!!!!