help on formula

[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?

 

[Frank Kabel]

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

 

[Domenic]

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!

 

[hgrove]

Frank Kabel wrote...

try
=SUMPRODUCT((MID("!XX" & import_source,
FIND("!", import_source)+1, 2)=B13)*(dr_cr_ind="DR"),amount)

...

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

 

[Frank Kabel]

Hi Harlan

Frank Kabel wrote...
..

Do you ever test your formula?

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".

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

 

[Domenic]

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)

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)

Interesting approach!

 

[Tang]

Thanks. You all are so kind.

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!