error sumproduct #value!

[Tufail]

hello all,
i am using following sumproduct formula ang facing this error #value !
=SUMPRODUCT((CashBook!$J$24:$J$15098=PL!$C6)*(CashBook!$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

 

[Bob Phillips]

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

 

[Tufail]

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]

What do you mean by carrying formula?

 

[Tufail]

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)*(CashBook!$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]

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

 

[Tufail]

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]

Aaah, now I get the error.

Try changing the formula in P24 to

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

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Tufail]

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

 

[Tufail]

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

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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