Hi,

I have nine columns say column A to column I and each of them contain

some data and I have another column called "Code".

If column A is not empty ( even though the other column contain data)

then code=A

If column A is empty but column A is not empty then Code=B

.....and so on.

For cells empty in column A through column I, I use Code"excluded"

I use formula

=IF((A2<>""),"A",IF((b2<>""),"B",IF((c2<>""),"C",IF((d2<>""),"D",if((e2<>""),"E",if((f2<>""),"F",If((g2<>""),"G",if((h<>""),"H",

if((i2<>""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

Hi,

I have nine columns say column A to column I and each of them contain

some data and I have another column called "Code".

If column A is not empty ( even though the other column contain data)

then code=A

If column A is empty but column A is not empty then Code=B

.....and so on.

For cells empty in column A through column I, I use Code"excluded"

I use formula

=IF((A2<>""),"A",IF((b2<>""),"B",IF((c2<>""),"C",IF((d2<>""),"D",if((e2<>""),"E",if((f2<>""),"F",If((g2<>""),"G",if((h<>""),"H",

if((i2<>""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

Thanks

Jenny

I have nine columns say column A to column I and each of them contain

some data and I have another column called "Code".

If column A is not empty ( even though the other column contain data)

then code=A

If column A is empty but column A is not empty then Code=B

.....and so on.

For cells empty in column A through column I, I use Code"excluded"

I use formula

=IF((A2<>""),"A",IF((b2<>""),"B",IF((c2<>""),"C",IF((d2<>""),"D",if((e2<>""),"E",if((f2<>""),"F",If((g2<>""),"G",if((h<>""),"H",

if((i2<>""),"i","excluded")))))))))

But I found I can't have over 7 multiple ifs.

Can anyone give me a advice? How to fix my formula?

Thanks

Jenny

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNTA(A1:I1),CHAR(64+MATCH(FALSE,ISBLANK(A1:I1),FALSE)),"Excluded")

In article < XXXX@XXXXX.COM >,

=IF(COUNTA(A1:I1),CHAR(64+MATCH(FALSE,ISBLANK(A1:I1),FALSE)),"Excluded")

In article < XXXX@XXXXX.COM >,

Thanks it works great.

My another question is that my boss want me to show location instead of

A through I.

Like

=IF((A2<>""),"SFO",IF((b2<>""),"NY",IF((c2<>""),"LA",IF((d2<>""),"DC",if((e2<>""),"

UT",if((f2<>""),"WA",If((g2<>""),"HI",if((h<>""),"JP",

if((i2<>""),"CH","excluded")))))))))

Can you help me with it?

Thanks a lot

Jenny

My another question is that my boss want me to show location instead of

A through I.

Like

=IF((A2<>""),"SFO",IF((b2<>""),"NY",IF((c2<>""),"LA",IF((d2<>""),"DC",if((e2<>""),"

UT",if((f2<>""),"WA",If((g2<>""),"HI",if((h<>""),"JP",

if((i2<>""),"CH","excluded")))))))))

Can you help me with it?

Thanks a lot

Jenny

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNTA(A2:I2),CHOOSE(MATCH(FALSE,ISBLANK(A2:I2),FALSE),

"SFO","NY","LA","DC","UT","WA","HI","JP","CH"),"excluded")

In article < XXXX@XXXXX.COM >,

=IF(COUNTA(A2:I2),CHOOSE(MATCH(FALSE,ISBLANK(A2:I2),FALSE),

"SFO","NY","LA","DC","UT","WA","HI","JP","CH"),"excluded")

In article < XXXX@XXXXX.COM >,

1. New IFS interface (jfs.ifs, udf.ifs for example)

2. multiple ifs. multiple fields, multiple criteria

3. Regarding multiple IF's using "OR"

4. Multiple IF's Using Formulas from Other Cells

6. IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

7. multiple ifs, mutliple fields, multiple criteria

12. Too many IFs and VLOOKUPs????

14. Nested IF's and VLookup Tables

15. Using VLOOKUP for returning multiple values and summing them

5 post • Page:**1** of **1**