How to use ISERROR in the following Formula ?

[RapchikM]

Hello
Formula obtained from Missing Numbers From a Range
=SMALL(IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8))

How Can i use ISERROR in above syntax/Formula ?

I don't know whether the above link reference is misleading because when going through it I got confused when
i read the following

COUNTIF($B$3:$D$5, ROW($1:$9)

becomes

COUNTIF({1,3,4; 5,6,7; 8,8,3},{1; 2; 3; 4; 5; 6; 7; 8; 9})

and returns

{1;0;2;1;1;1;1;2;0}.

Because 0 or Zero indicates "TRUE" and 1 indicates "FALSE" So where does 2 Come from ?
So Basically i wanted to avoid Error #NUM

Thanks
Rapchik

 

[nemmi69]

Can be used

=IF(ISERROR(COUNTIF($B$3:$D$5, ROW($1:$9)),"Is an error",COUNTIF($B$3:$D$5, ROW($1:$9))

 

[RapchikM]

Sorry the below gave Error

=IF(ISERROR(COUNTIF($B$3:$D$5, ROW($1:$9)),"Is an error",COUNTIF($B$3:$D$5, ROW($1:$9))

Too many arguments Entered

RapchikM

 

[nemmi69]

sorry, try

=IF(ISERROR(COUNTIF($B$3:$D$5,ROW($1:$9))),"Is an error",COUNTIF($B$3:$D$5,ROW($1:$9)))

 

[RapchikM]

Tried

Now values are in 0 and 1 instead of Missing Numbers

RapchikM

 

[Fluff]

How about either of these

Fluff.xlsm
	I	J	K	L	M	N
1
2	1	3	4		2	2
3	5	6	7		9	9
4	8	8	3
5
6
7
8
Master

Cell Formulas
Range	Formula
M2:M3	M2	=FILTER(SEQUENCE(9),ISNA(MATCH(SEQUENCE(9),TOCOL(I2:K4),0)))
N2:N7	N2	=IFERROR(SMALL(IF(COUNTIF($I$2:$K$4, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(N$3:N3)),"")
Dynamic array formulas.

 

[RapchikM]

Fluff Sir,

Thank you so much for valuable help. Indeed i 've tried the N2 Formula as this is in mind since morning IFERROR and SMALL. Just Perfect
Tried M2 Formula but gave me #CALC! Error.

The Link posted as per #1 of this thread Is it misleading between 0, 1 and 2. TRUE =0 and 1=FALSE How does 2 come ? or is it typo Error

RapchikM

 

[Fluff]

Tried M2 Formula but gave me #CALC! Error.

Do you have the TOCOL function?

TRUE does not equal 0.
The array {1;0;2;1;1;1;1;2;0} shows how many times each number from 1 to 9 occurs in the range.
So 1 occurs once 2 is missing, 3 occurs twice etc.

 

[fjns]

Hello
Formula obtained from Missing Numbers From a Range
=SMALL(IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8))

How Can i use ISERROR in above syntax/Formula ?

I don't know whether the above link reference is misleading because when going through it I got confused when
i read the following


Because 0 or Zero indicates "TRUE" and 1 indicates "FALSE" So where does 2 Come from ?
So Basically i wanted to avoid Error #NUM

Thanks
Rapchik

Hi, I created a table with the explanation.

Analysis.xlsx

 

[fjns]

I added two more sentences to the table.