multi level formul ifs counta mistake

[rjmdc]

hi
this is the original formula
=@IFS([@TABS]="","",AND([@TABS]<>"",COUNTA(Table2[@[Packet Ready to Print]:[Signed Doc Received snail mail]])=0), 7,AND([@TABS]<>"",[@[Packet Ready to Print]]="To Print"),5,AND([@TABS]<>"",[@[Packet Ready to Print]]="printed",[@[Packet Sent ]]=""),4,AND([@TABS]<>"",[@[Launched Date
DONE]]<>"",[@[Signed Doc Received Email]]="",[@[Signed Doc Received snail mail]]=""), 6,AND([@TABS]<>"",[@[Launched Date
DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,AND([@TABS]<>"",[@[Packet Sent ]]<>"",[@[Meeting Date]]=""),3,AND([@TABS]<>[@[Meeting Date]]<>"",[@[Launched Date
DONE]]=""),1,AND([@TABS]<>"",[@[Invite Sent]]<>""),2)

i need to add: AND([@TABS]<>"",[@[Invite Sent]]="TERMINATED"),8 but itserroring since i dont know where to put it

 

[RoryA]

You don't need to keep testing [@TABS]<>"" since the first part of your formula already covers what will happen if it is "". That new condition needs to go before the last one as you are testing the same column:

=@IFS([@TABS]="","",COUNTA(Table2[@[Packet Ready to Print]:[Signed Doc Received snail mail]])=0, 7,[@[Packet Ready to Print]]="To Print",5,AND([@[Packet Ready to Print]]="printed",[@[Packet Sent ]]=""),4,AND([@[Launched Date
DONE]]<>"",[@[Signed Doc Received Email]]="",[@[Signed Doc Received snail mail]]=""), 6,AND([@[Launched Date
DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,AND([@[Packet Sent ]]<>"",[@[Meeting Date]]=""),3,AND([@[Meeting Date]]<>"",[@[Launched Date
DONE]]=""),1,[@[Invite Sent]]="TERMINATED",8,[@[Invite Sent]]<>"",2)

 

[rjmdc]

how do i do that since invite sent not blank is 2 i need not blank and not terminated is 2 and if terminated 8
please help me
thanks

 

[RoryA]

Did you try the formula I posted?

 

[rjmdc]

says problem with the formula

 

[RoryA]

I've just created a table with those columns and the formula is fine.

 

[rjmdc]

this is what i get

 

[rjmdc]

this is what i copied
=@IFS([@TABS]="","",COUNTA(Table2[@[Packet Ready to Print]:[Signed Doc Received snail mail]])=0, 7,[@[Packet Ready to Print]]="To Print",5,AND([@[Packet Ready to Print]]="printed",[@[Packet Sent ]]=""),4,AND([@[Launched Date
DONE]]<>"",[@[Signed Doc Received Email]]="",[@[Signed Doc Received snail mail]]=""), 6,AND([@[Launched Date
DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,AND([@[Packet Sent ]]<>"",[@[Meeting Date]]=""),3,AND([@[Meeting Date]]<>"",[@[Launched Date
DONE]]=""),1,[@[Invite Sent]]="TERMINATED",8,[@[Invite Sent]]<>"",2)

 

[RoryA]

Yes, I just pasted that from your post into a table and it worked. I suspect one of the column names is not right. Can you copy and paste your original formula from the forum into the table?

 

[rjmdc]

explain
what should i do with the original formula?