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]

It seems you won't follow my instructions, so let's try something else. Just amend the last part of your current formula from:

AND([@TABS]<>"",[@[Invite Sent]]<>""),2)

to:

[@[Invite Sent]]="TERMINATED",8,[@[Invite Sent]]<>"",2)

 

[rjmdc]

i am not sure what i am not following
i repasted my original formula
i startedwith the error of #NA
now i undertsand the issue
the tabs IS blank anbd invite sent is terminated
how can i fix the #NA to be 8

 

[rjmdc]

the error begins in the tabs column
this was the formula
=IF(ISBLANK(XLOOKUP([@Name],Broker_CM!A:A,Broker_CM!B:B)),"", XLOOKUP([@Name],Broker_CM!A:A,Broker_CM!B:B))

it gives me the #NA
then all the other forumals fail

 

[RoryA]

XLOOKUP has an argument you can supply to return something if the lookup fails:

XLOOKUP([@Name],Broker_CM!A:A,Broker_CM!B:B,"")

 

[rjmdc]

hi fixed all my xlookips
now how do i add if invite =terminated 8?

 

[RoryA]

As I said in post 21.

 

[rjmdc]

i did that adding to my original formula however it defaulted to scenario 4 instead of the 8
no longer are there blank tabs per se
there are blank rows

 

[RoryA]

Then you need to rearrange the conditions in the relevant priority. The IFS will return the value for whichever is the first condition that is true.

 

[rjmdc]

will try

 

[Alex Blakenburg]

In case it helps:
PS: if none of the conditions are met you will currenltly get #N/A, I have added a catch all at the end.
None of the conditions are met if Tab <> "" and the only items populated are one or more of Packet Ready to Print (invalid entry), Signed Doc Received Email, Signed Doc Received snail mail

20231012 IFS formula rjmdc.xlsx
	A	B	C	D	E	F	G	H	I	J
8	Tabs	Packet Ready to Print	Packet Sent	Launched Date DONE	Signed Doc Received Email	Meeting Date	Invite Sent	Signed Doc Received snail mail	Formula	Criteria
9										[@Tabs]=""
10	Test								7	COUNTA(Table2[@[Packet Ready to Print]:[Signed Doc Received snail mail]])=0, 7,
11	2	To Print	x	x	x	x	x	x	5	[@[Packet Ready to Print]]="To Print",5,
12	3	Printed							4	AND([@[Packet Ready to Print]]="printed",[@[Packet Sent ]]=""),4,
13	3		x	Yes					6	AND([@[Launched Date DONE]]<>"",[@[Signed Doc Received Email]]="",[@[Signed Doc Received snail mail]]=""), 6,
14	3	Printed	x	Anything	x	x	x	x	0	AND([@[Launched Date DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,
15	4		x	Yes	Yes	x	x	x	0	AND([@[Launched Date DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,
16	4		x	Yes	x	x	x	Yes	0	AND([@[Launched Date DONE]]<>"",OR([@[Signed Doc Received Email]]<>"",[@[Signed Doc Received snail mail]]<>"")),0,
17	5		Yes						3	AND([@[Packet Sent ]]<>"",[@[Meeting Date]]=""),3,
18	6					Yes			1	AND([@[Meeting Date]]<>"",[@[Launched Date DONE]]=""),1,
19	5						TERMINATED		8	[@[Invite Sent]]="TERMINATED",8,
20	6	xx					Something		2	[@[Invite Sent]]<>"",2,
21	7	x			x			x	No Criteria Met	TRUE,"No Criteria Met")
Formula

Cell Formulas
Range	Formula
I9:I21	I9	=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, TRUE,"No Criteria Met")