Counting Number If Not Sequence

[muhammad susanto]

hi all..
i have a list.. but into list contains "jumping" number / not series..
how to determine , count of number not series / not sequence

date Query function with date filter.xlsx
	G
2	sequences list
3	1
4	2
5	3
6	5
7	8
8	9
9	10
10	11
11	13
12	14
13	17
Sheet1

the answer is 6, how to determine it?
6 from (4,6,7,12.15.16 as jumping number)
please, don't use helper column
thank in advance

susanto

 

[JEC]

You could try this matrix (could be matrix, close with ctrl+shft+return)

=SUMPRODUCT(--ISERROR(MATCH(ROW(INDIRECT("1:"& MAX(G3:G13)));G3:G13;0)))

 

[muhammad susanto]

hi JEC, thank you...working well..
Can't without Ctrl+Ship+Enter?

 

[JEC]

Not in your Excel version

 

[muhammad susanto]

sorry JEC, when i change number in a cell (not last cell) ...the result not work.
i'm using Excel 2013 not 365

 

[JEC]

It should work when you add numbers

 

[StephenCrump]

sorry JEC, when i change number in a cell (not last cell) ...the result not work.

Here are some other formulae that may work for you, depending on what your numbers might look like (you've only posted one sample).

	A	B	C	D
1	Ascending,	Random,	Random,
2	No duplicates	No duplicates	With duplicates	JEC
3	1	14	14	14
4	2	3	3	3
5	3	1	2	2
6	5	13	3	3
7	8	2	3	3
8	9	10	5	5
9	10	9	9	9
10	11	11	11	11
11	13	17	17	17
12	14	5	5	5
13	17	8	8	8
14
15	6	6	8	8
Sheet1

Cell Formulas
Range	Formula
A15	A15	=A13-A3-COUNT(A3:A13)+1
B15	B15	=MAX(B3:B13)-MIN(B3:B13)-COUNT(B3:B13)+1
C15	C15	=MAX(C3:C13)-MIN(C3:C13)-SUMPRODUCT(1/COUNTIF(C3:C13,C3:C13))+1
D15	D15	=SUMPRODUCT(--ISERROR(MATCH(ROW(INDIRECT(MIN(D3:D13) & ":" & MAX(D3:D13))),D3:D13,0)))

 

[muhammad susanto]

if number update not in last cell (number 20), the result is wrong
my data ascending and not duplicate

 

[bebo021999]

Alternative non-array formula, unique number in any order:

=SUMPRODUCT(--(FREQUENCY($G$3:$G$13,ROW(INDIRECT(MIN(G3:G13)&":"&MAX(G3:G13))))=0))-1

Enter only.

 

[StephenCrump]

if number update not in last cell (number 20), the result is wrong
my data ascending and not duplicate

Sorry, I don't understand. Why is 9 wrong?

The sequence is missing 9 numbers (8,10,12,13,14,15,17,18, and 19)