“IFS” does not process correctly

[Genki_Tokyo]

I'm sorry if it's hard to understand because I'm translating the question from Japanese into English using translation software.

IFS(
AND([ColumnA]<>"",[ColumnB]=""),[ColumnA].[ColumnC],　//conditions1
AND([ColumnB]<>"",[ColumnA]=""),[ColumnB].[ColumnD],　//conditions2
)

TYPE：TEXT

Input location：App formula

conditions1 　→OK

conditions2　→Unresponsive

As a test

virtual Column　

IFS(
AND([ColumnA]<>"",[ColumnB]=""),[ColumnA].[ColumnC],　//conditions1
AND([ColumnB]<>"",[ColumnA]=""),[ColumnB].[ColumnD],　//conditions2
)

conditions1 　→OK

conditions2　→OK

Is it possible to calculate without using a “virtual column”?

I would like to use it for a regular "Column"

I don't know the cause

[Genki_Tokyo]

IFS(
AND([ColumnA]<>"",[ColumnB]=""),[ColumnA].[ColumnC],　//conditions1
AND([ColumnB]<>"",[ColumnA]=""),[ColumnB].[ColumnD],　//conditions2
AND([ColumnA]<>"",[ColumnB]<>""),"ERROR"　//conditions3
)

virtual Column

[ColumnA]＝INPUT、[ColumnB]＝NULL　→　ERROR　<Why？Why？Why？Why？>

[ColumnA]＝NULL、[ColumnB]＝INPUT　→　OK

[ColumnA]＝INPUT、[ColumnB]＝INPUT　→　ERROR

I don't understand too much

[AleksiAlkio]

Try with this and see what is the result..
IFS(
AND([ColumnA]<>"",[ColumnB]=""),"1st result",
AND([ColumnB]<>"",[ColumnA]=""),"2nd result",
AND([ColumnA]<>"",[ColumnB]<>""),"3rd result"
)

[Genki_Tokyo]

It will be like this

IFS(
AND([ColumnA]<>"",[ColumnB]=""),"1st result",　→"3rd result"
AND([ColumnB]<>"",[ColumnA]=""),"2nd result",　→"2nd result"
AND([ColumnA]<>"",[ColumnB]<>""),"3rd result"　→"3rd result"
)

[AleksiAlkio]

i made a quick test exactly with the same formula. All columns are text type columns, and for me it works just fine.

[Genki_Tokyo]

Thank you for checking.

That's usually the case.

I will verify if there is a problem with "ref".

thank you very so much.

[AleksiAlkio]

You're welcome!

[Genki_Tokyo · Accepted solution]

i did it!!

I solved it with this.

IFS(
AND((ISNOTBLANK([ColumnA]),ISBLANK([ColumnB])),"1st result",
AND((ISNOTBLANK([ColumnB]),ISBLANK([ColumnA])),"2nd result",
AND((ISNOTBLANK([ColumnA]),(ISNOTBLANK([ColumnB])),"3rd result"
)

It's a tedious task, but I'm OK with it.

Thank you very much for your advice again and again.

[AleksiAlkio]

Okay good, but still it's a mystery why the other formula didn't work for you as it should.

[Genki_Tokyo]

It is just like you said

[AleksiAlkio]

First check the option from Settings > Data > Legacy > Blank value comparison mode is set as "Consistent".

[Genki_Tokyo]

thank you for the message.

Set to "Consistent"

But it's not calculated

[AleksiAlkio]

By any chance, the value [ColumnA].[ColumnC] is "ERROR"? I assume the [ColumnA ] is Ref or Enum/Ref column type.

[Genki_Tokyo]

[ColumnA]、[ColumnB]　is Ref 

 The conditional expression "AND([ColumnB]<>"",[ColumnA]="")" reacts correctly.