help on formula

  • Thread starter Thread starter Tang
  • Start date Start date
T

Tang

i can't figure out why this formula give me #value!

the formula:

=SUMPRODUCT((MID(import_source,FIND("!",import_source)+1,2)=B13)*(dr_cr_ind=
"DR"),amount)
where:
import_source =JVES!$S$2:$S$3000
dr_cr_ind=JVES!$Q$2:$Q$3000
amount =JVES!$P$2:$P$3000
B13 = IH

my formula is sum the amount for the data in import_source with IH after 1st
"!".

example of the data in import_source is as follow:
113185023!IB-MBBN2879!3254153554!06/01/04!FT OUTGOING!-4900002!
70081016!IH-CTB2880!3254153738!06/01/04!FT OUTGOING!-530002!
70081016!IH-CTB2880!3254153738!06/01/04!FT OUTGOING!-530002!

i suspect the error is caused by FIND("!",import_source), but don't know why
can't work. Is it Find don't work under array?
 
Hi
try
=SUMPRODUCT((MID("!XX" &
import_source,FIND("!",import_source)+1,2)=B13)*(dr_cr_ind=
"DR"),amount)

Problem ist that if FIND does not find a match it does return the
#VALUE error
 
Hi Tang,

The following array formula should take care of instances where FIND
finds no match:

=SUM(IF((ISNUMBER(FIND("!",import_source))),(MID(import_source,FIND("!",i
mport_source)+1,2)=B13)*(dr_cr_ind="DR")*amount))

entered using CONTROL+SHIFT+ENTER

Hope this helps!
 
Frank Kabel wrote...
try
=SUMPRODUCT((MID("!XX" & import_source,
FIND("!", import_source)+1, 2)=B13)*(dr_cr_ind="DR"),amount)
Click to expand...
...

Do you ever test your formula?

You haven't augmented import_source *inside* FIND, so it'd still giv
errors when there were no ! in it. Also, your approach could giv
incorrect results when B13 == "XX".

Dominic's approach is more robus, but too long. Better something like

=SUMPRODUCT(ISNUMBER(1/(MID(import_source,
FIND("!",import_source)+1,2)=B13))*(dr_cr_ind="DR"),amount)

There's also an alternative approach that uses two fewer neste
function call levels.

=SUMPRODUCT((SUBSTITUTE(import_source,"!"&B13,"",1)<>import_source)*(dr_cr_ind="DR"),amount
 
Hi Harlan
Frank Kabel wrote...
..

Do you ever test your formula?
Click to expand...

in most cases yes :-)
In this case I just messed the copy+paste process

You haven't augmented import_source *inside* FIND, so it'd still give
errors when there were no ! in it. Also, your approach could give
incorrect results when B13 == "XX".
Click to expand...

Agreed this would return an invalid result. I just used the OP's very
restrict specs (but agreed this could be only an example)
So Domenics (and your's) is a more robust approach :-)

Frank
 
hgrove said:
Dominic's approach is more robus, but too long. Better something like

=SUMPRODUCT(ISNUMBER(1/(MID(import_source,
FIND("!",import_source)+1,2)=B13))*(dr_cr_ind="DR"),amount)
Click to expand...

The drawback with this formula is that if any one cell doesn't contain
an exclamation mark, the formula will return an error. Although,
judging by the format, it's highly unlikely. But my formula would deal
with such a case, if it were present.
There's also an alternative approach that uses two fewer nested
function call levels.

=SUMPRODUCT((SUBSTITUTE(import_source,"!"&B13,"",1)<>import_source)*(dr_cr_ind
="DR"),amount)
Click to expand...

Interesting approach!
 
Thanks. You all are so kind.

Domenic said:
The drawback with this formula is that if any one cell doesn't contain
an exclamation mark, the formula will return an error. Although,
judging by the format, it's highly unlikely. But my formula would deal
with such a case, if it were present.


Interesting approach!
Click to expand...
 
Back
Top