Prev: Using vba find
Next: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!!
From: Gladiator on 28 Apr 2010 13:13
Hi all,
This formula sometimes works and sometimes shows #VALUE! error. Please
anyone advice. Thanks.
From: Gladiator on 28 Apr 2010 13:16
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.
From: Ayo on 28 Apr 2010 13:16
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: Rick Rothstein on 28 Apr 2010 13:21
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" <Gladiator(a)discussions.microsoft.com> wrote in message
news:3037E3E4-8F32-4EFC-90B1-B0A523345CA9(a)microsoft.com...
> Hi all,
> This formula sometimes works and sometimes shows #VALUE! error. Please
> anyone advice. Thanks.

From: Gladiator on 28 Apr 2010 13:24
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.
 |  Next  |  Last
Pages: 1 2
Prev: Using vba find
Next: Help with SORT property in xl2007 and xl2007 !!!!!!!!!!!!!!!!!!!!