array function!

[jimmy brasser]

For each shipset I would like excel to add up all the several damag
types. The damage types are all in 1 cell:
"2D" should give the value 2 (in a cell) for Shipset 001. This count
for every Shipset and "7" or "4" are also damage types. Can someon
help me!

Shipset Damage
001 2A, 2B, 2D, 3C
001 1A
001 2D
001 7
001 4
002 1A
002 7
003 2A, 2C, 2D
003 2D
003 3C
003 #N/A
003 1A

So it must be some kind of an array sum-function searching throug
textstrings....

Help

 

[Ron Rosenfeld]

For each shipset I would like excel to add up all the several damage
types. The damage types are all in 1 cell:
"2D" should give the value 2 (in a cell) for Shipset 001. This counts
for every Shipset and "7" or "4" are also damage types. Can someone
help me!

Shipset Damage
001 2A, 2B, 2D, 3C
001 1A
001 2D
001 7
001 4
002 1A
002 7
003 2A, 2C, 2D
003 2D
003 3C
003 #N/A
003 1A

So it must be some kind of an array sum-function searching through
textstrings....

*array-enter*:

=SUM((Shipset=1)*NOT(ISERROR(FIND("2D",Damage))))

Shipset and Damage are named ranges.

If the Shipset your testing is in D1 and the Damage type is in E1, then:

=SUM((Shipset=D1)*NOT(ISERROR(FIND(E1,Damage))))




--ron

 

[jimmy brasser]

*hmm, both formula's return error type #NUM!*

Shipset is in colum A1, Damage B1

The thing is that 1 damage type (such as B2 or 4) must be summed u
separately for each shipset. Because it "repeats" itself in th
worksheet like this:

001____2A, 2B, 2D, 3C
001____1A
001____2D
001____7
001____4
002____1A
002____7
003____2A, 2C, 2D
003____2D
003____3C
003____#N/A
003____1A
001____3C
001____1A, 5
001____2D, 2C
001____7
001____3A
002____2B
002____#N/A
003____2C
003____2D
003____3C
003____2D
003____2C

Help

 

[Ron Rosenfeld]

*hmm, both formula's return error type #NUM!*

Shipset is in colum A1, Damage B1

The thing is that 1 damage type (such as B2 or 4) must be summed up
separately for each shipset. Because it "repeats" itself in the
worksheet like this:

001____2A, 2B, 2D, 3C
001____1A
001____2D
001____7
001____4
002____1A
002____7
003____2A, 2C, 2D
003____2D
003____3C
003____#N/A
003____1A
001____3C
001____1A, 5
001____2D, 2C
001____7
001____3A
002____2B
002____#N/A
003____2C
003____2D
003____3C
003____2D
003____2C

Help!

I don't understand why you are getting a #NUM! error.

What version of XL are you using?

What happens when you follow the troubleshooting procedure in HELP for
correcting a #NUM! error?

If the Shipset is text, and not numbers, then the formula should be:

=SUM((--Shipset=1)*NOT(ISERROR(FIND("2D",Damage))))

or

=SUM((Shipset="001")*NOT(ISERROR(FIND("2D",Damage))))

Again -- *Array-entered* (hold down <ctrl><shift> while hitting <enter>).

But I can't get a #NUM! error using your data as posted. And changes I make
only seem to result in #VALUE! errors, and not #NUM! errors.

I suspect there's something different about the data you are actually using, vs
what you have posted.


--ron

 

[jimmy brasser]

Hi Ron,

thanks for your help! I was struggling with range (as you wrote)! I se
up the wrong reference: I included the name (shipset) with the range
So it came up with the #value! error.

The formula works great! *A big thanks!*

I got 1 (final) question: why the NOT(ISERROR... function in th
formula? I've been searching through the excel help-topic and iserro
refers to any error value. What's with that?

 

[Ron Rosenfeld]

I got 1 (final) question: why the NOT(ISERROR... function in the
formula? I've been searching through the excel help-topic and iserror
refers to any error value. What's with that?!

If DAMAGE code does not appear in any line in the DAMAGE range,
FIND("2D",Damage) returns the #VALUE! error value. So the result of the FIND
function might be something like:

{9;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;9;1;#VALUE!;#N/A;#VALUE!}

If you tried to SUM that, you would get #VALUE!.

By applying the NOT(ISERROR( construct you obtain:

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}

In the subsequent multiplication, the TRUE's get interpreted as a 1.

You could obtain the same result by using the ISNUMBER function, and that would
actually be a bit simpler. I used the NOT(ISERROR simply out of habit and
ISNUMBER would actually be more efficient.

So, to simplify, array-enter:

=SUM((Shipset=1)*ISNUMBER(FIND("2D",Damage)))


--ron