IFYESNO

[schardt679]

IFYESNO is like IFERROR for Yes and No. It returns Value_If_Yes for Yes cells, Value_If_No for No cells, and the array itself otherwise. Uses ISOMITTED!

I found a good use for ISOMITTED. Use it if you want a default value other than an empty string or 0 for an argument.
This works because ISOMITTED treats empty strings, empty references, and 0s differently than missing values.
If Value_If_Yes or Value_If_No reference an empty cell, 0 is returned. To return an empty string, attach &"" to the reference.
This is exactly how IFERROR's Value_If_Error works too. If Value_If_Yes or Value_If_No are omitted, 1 or 0 are used respectively.
If skipping arguments, a comma is required. Calls IFBLANK.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =IFYESNO(Array☛ range to check for Yes's & No's, [Value_If_Yes]☛ value to return for Yes cells; =1, [Value_If_No]☛ value to return for No cells; =0) ⁂[]=optional; =default; =omit

=LAMBDA(Array,[Value_If_Yes],[Value_If_No],
      LET(Arr, IFBLANK(Array, ""),          Yes, Value_If_Yes,          No, Value_If_No,
             Result, IF(Arr="YES", IF(ISOMITTED(Yes), 1,  Yes),  Arr),
             Return, IF(Result="NO", No,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J
1		IFYESNO
2
3		Original Data			Value_If_Yes:			Result
4		Product	Ordered?		Value_If_No:			Ordered?	Total Orders
5		Pepper Deseeder	Yes					1	5
6		Knife Set	No					0
7		Cutting Board	YES					1
8		Peeler
9		Spatula	NO					0
10		Meat Tenderizer	YEs					1
11		Cast Iron 5-piece Set	1					1
12		Serving Platter	0					0
13		Dinnerware 16-piece Set	yes					1
14
15		Formula in cell H5☛ =IFYESNO(C5:C13)
16
17
18
19		Original Data			Value_If_Yes:	Good		Result
20		Question 1	Question 2		Value_If_No:	Bad		Question 1	Question 2
21		Yes	No					Good	Bad
22		No	nO					Bad	Bad
23		NO	yeS					Bad	Good
24		YES						Good
25		#N/A	yes					#N/A	Good
26
27		Formula in cell H21☛ =IFYESNO(B21:C25, F19, F20)
28
29
30
31		Original Data			Value_If_Yes:	0		Result
32		Question 1	Question 2		Value_If_No:	1		Question 1	Question 2
33		Yes	No					0	1
34		No	nO					1	1
35		NO	yeS					1	0
36		YES	YeS					0	0
37		no	yes					1	0
38
39		Acting like NOT for TRUE/FALSE
40		Formula in cell H33☛ =IFYESNO(B33:C37, F31, F32)
41
IFYESNO

Cell Formulas
Range	Formula
H5:H13	H5	=IFYESNO(C5:C13)
I5	I5	=SUM(H5#)
B15	B15	=AFORMULATEXT(H5)
H21:I25,H33:I37	H21	=IFYESNO(B21:C25, F19, F20)
B27	B27	=AFORMULATEXT(H21)
B40	B40	=AFORMULATEXT(H33)
Dynamic array formulas.