LET Function Letting Me Down

[Jeffrey Mahoney]

I'm trying to check how many IDs in column A that are not in column E. This is a simple example; my real data is different. Column A looks at column B and normally returns a portion of of the values in column. That formula leaves a blank, but not a true blank. So using Counta(A5:A14) returns 10 even though there are only 4 items. So I have to filter the items for true non-blanks.

The formula in Cell A2 has embedded filtered formulas for columns A and E, then doing the calculation and does not work. While the formula in cell D2 is using filters created in cells C5 and F5 respectively and does work.

Why doesn't the PIDS and PROJS variables store the proper data so the Counta and Countif functions can work? Do I need to reference the array differently when it comes from within the formula? As you can see in the screenshot below, the filter is providing the correct array.

Book3
	A	B	C	D	E	F
1	Missing			Missing
2	#VALUE!			1
3	10	4	4		10	4
4	PID	ID	Filter		Projects	Filter
5	A	A	A		A	A
6	B	B	B		C	C
7	C	C	C		D	D
8	D	D	D		E	E
9
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
A2	A2	=LET(PIDS,FILTER($A$5:$A$14,$A$5:$A$14<>""),PROJS,FILTER($E$5:$E$14,$E$5:$E$14<>""),COUNTA(PIDS)-SUM(COUNTIF(PROJS,PIDS)))
D2	D2	=COUNTA(C5#)-SUM(COUNTIF(F5#,C5#))
A3:C3,E3:F3	A3	=COUNTA(A5:A14)
C5:C8	C5	=FILTER(A5:A14,A5:A14<>"")
F5:F8	F5	=FILTER(E5:E14,E5:E14<>"")
A5:A14	A5	=IF(B5<>"",B5,"")
Dynamic array formulas.

 

[Fluff]

The Criteria_range in the countifs must be a range, but the filter function returns an array.

 

[Dave Patton]

Try the following

Let_a.xlsm
	A				E
2	1
3
4	PID				Projects
5	A				A
6	B				C
7	C				D
8	D				E
9
5b

Cell Formulas
Range	Formula
A2	A2	=COUNTA(FILTER(A5:A8,NOT(COUNTIF(E5:E8,A5:A8))))
A5:A9	A5	=IF(B5<>"",B5,"")

 

[Jeffrey Mahoney]

@Fluff The Countifs function hasn't been updated to accept arrays. I wonder if that will change!?

Ok, the 'NOT' part! . . . I would've never considered it could be used to evaluate ranges or arrays, just math or text matching boolean results. That is so simple looking, but I had to work my way through the logic. Breaking it down inside out.
COUNTIF(E5:E14,A5:A14) = {1;0;1;1;6;6;6;6}
NOT(COUNTIF(E5:E14,A5:A14)) = {F;T;F;F;F;F;F;F;F;F} (T=TRUE, F=FALSE) The zero becomes TRUE
FILTER(A5:A14,NOT(COUNTIF(E5:E14,A5:A14))) = {"B"} Not found on column E list
COUNTA(FILTER(A5:A14,NOT(COUNTIF(E5:E14,A5:A14)))) = 1

Thank you Dave for the formula

 

[Dave Patton]

Thanks for the feedback.

 

[Fluff]

I wonder if that will change!?

I very much doubt it.

 

[FormR]

Just another option, also - I'm not sure if the formula in post #3 returns a good result when everything in col A has a match in col E?

Book1
	A	B	C	D	E
1
2	1
3
4	PID				Projects
5	A				A
6	B				C
7	C				D
8	D				E
Sheet1

Cell Formulas
Range	Formula
A2	A2	=SUM(--ISNA(MATCH(A5:A8,E5:E8,0)))

 

[Jeffrey Mahoney]

@FormR - No, when all matched the filter portion returned #CALC which would always return at least 1. I tested your formula and it worked great. I did have to switch the two ranges inside the Match function to get it to give me missing values from column E.