error sumproduct #value!
 

hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

tnx for posting, if i change col_N insted col_P which don't have formula then
there isn't any error coming out, so i think that's problam with carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have formula
then
there isn't any error coming out, so i think that's problam with carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

i means range $P$24:$P$15098 have this formula
=IF(AND($M24="USD",ISNUMBER($N24+$O24)),$N24*$O24, IF($M24="JPY",$N24,""))
if i change my range like $N$24:$N$15098 <-- my this range don't have any
formula, in this case there is not any error, so i think this is due to
contaning formula in col_P, hope you understand now and would help me on this
error.

=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
9,00 114.14 1,090,000.



"Bob Phillips" wrote:

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have formula
then
there isn't any error coming out, so i think that's problam with carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

I have knocked up a simple example using your formulae, and it works fine
for me. I still think it is a data error.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
i means range $P$24:$P$15098 have this formula
=IF(AND($M24="USD",ISNUMBER($N24+$O24)),$N24*$O24, IF($M24="JPY",$N24,""))
if i change my range like $N$24:$N$15098 <-- my this range don't have any
formula, in this case there is not any error, so i think this is due to
contaning formula in col_P, hope you understand now and would help me on
this
error.

=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
9,00 114.14
1,090,000.



"Bob Phillips" wrote:

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have
formula
then
there isn't any error coming out, so i think that's problam with
carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value
!
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

ummmm....anyways really thank you very much for your attention....by the way
formula also working my side, but untill numric value are in cell, but where
is only formmula there is problam starting like follow:

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
1. 9,00 114.14
1,090,000. <-- Ok
2. 1,000 100.00
1,000,000. <-- Ok
3.
<-- Error start

from row 3~ not have any value, but formula is in Col_P, so actual problam
is starting from empty cell (by value).


"Bob Phillips" wrote:

I have knocked up a simple example using your formulae, and it works fine
for me. I still think it is a data error.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
i means range $P$24:$P$15098 have this formula
=IF(AND($M24="USD",ISNUMBER($N24+$O24)),$N24*$O24, IF($M24="JPY",$N24,""))
if i change my range like $N$24:$N$15098 <-- my this range don't have any
formula, in this case there is not any error, so i think this is due to
contaning formula in col_P, hope you understand now and would help me on
this
error.

=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
9,00 114.14
1,090,000.



"Bob Phillips" wrote:

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have
formula
then
there isn't any error coming out, so i think that's problam with
carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error #value
!
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

Aaah, now I get the error.

Try changing the formula in P24 to

=IF(OR(M24="",AND(N24="",O24="")),0,IF(AND($M24="U SD",ISNUMBER($N24+$O24)),$N24*$O24,IF($M24="JPY",$ N24,"")))

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
ummmm....anyways really thank you very much for your attention....by the
way
formula also working my side, but untill numric value are in cell, but
where
is only formmula there is problam starting like follow:

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
1. 9,00 114.14
1,090,000. <-- Ok
2. 1,000 100.00
1,000,000. <-- Ok
3.
<-- Error start

from row 3~ not have any value, but formula is in Col_P, so actual problam
is starting from empty cell (by value).


"Bob Phillips" wrote:

I have knocked up a simple example using your formulae, and it works fine
for me. I still think it is a data error.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
i means range $P$24:$P$15098 have this formula
=IF(AND($M24="USD",ISNUMBER($N24+$O24)),$N24*$O24, IF($M24="JPY",$N24,""))
if i change my range like $N$24:$N$15098 <-- my this range don't have
any
formula, in this case there is not any error, so i think this is due to
contaning formula in col_P, hope you understand now and would help me
on
this
error.

=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
9,00 114.14
1,090,000.



"Bob Phillips" wrote:

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have
formula
then
there isn't any error coming out, so i think that's problam with
carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error
#value
!
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

hahaha.....great....it's working....thank you very much....

"Bob Phillips" wrote:

Aaah, now I get the error.

Try changing the formula in P24 to

=IF(OR(M24="",AND(N24="",O24="")),0,IF(AND($M24="U SD",ISNUMBER($N24+$O24)),$N24*$O24,IF($M24="JPY",$ N24,"")))

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
ummmm....anyways really thank you very much for your attention....by the
way
formula also working my side, but untill numric value are in cell, but
where
is only formmula there is problam starting like follow:

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
1. 9,00 114.14
1,090,000. <-- Ok
2. 1,000 100.00
1,000,000. <-- Ok
3.
<-- Error start

from row 3~ not have any value, but formula is in Col_P, so actual problam
is starting from empty cell (by value).


"Bob Phillips" wrote:

I have knocked up a simple example using your formulae, and it works fine
for me. I still think it is a data error.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
i means range $P$24:$P$15098 have this formula
=IF(AND($M24="USD",ISNUMBER($N24+$O24)),$N24*$O24, IF($M24="JPY",$N24,""))
if i change my range like $N$24:$N$15098 <-- my this range don't have
any
formula, in this case there is not any error, so i think this is due to
contaning formula in col_P, hope you understand now and would help me
on
this
error.

=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))

Col_N(no any formula) Col_O(exchange rate) Col_P (above formula)
9,00 114.14
1,090,000.



"Bob Phillips" wrote:

What do you mean by carrying formula?

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
tnx for posting, if i change col_N insted col_P which don't have
formula
then
there isn't any error coming out, so i think that's problam with
carrying
formula, is there any salutation ?

"Bob Phillips" wrote:

Looks like there is a #VALUE in the data being analysed.

--
__________________________________
HTH

Bob

"Tufail" wrote in message
...
hello all,
i am using following sumproduct formula ang facing this error
#value
!
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

error sumproduct #value!
 

thank you so much.........


"Dave Peterson" wrote:

If you have text (even empty strings like "") in those cells, you'll see the
error.

You could try this syntax that will ignore the text values:

=SUMPRODUCT(--(CashBook!$J$24:$J$15098=PL!$C6),--(CashBook!$P$24:$P$15098))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Tufail wrote:

hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(Cash Book!$P$24:$P$15098))
coulmns "P" contaning formula as under:
Col_N Col_O Col_P
9,00 114.14 1,090,000.

thanks in advance

--

Dave Peterson