Excel - Find the number of occurrences of a number

January 2017


Issue


I want to know how many times a number repeats itself in the list below:
1 4  
2  
3  
4  
7 6  
8  
9  
10  
11  
12  
15 single 
17 3  
18  
19 

Solution


Try this formula :

=IF(A2=A3-1,"",IF(AND(A2<>A3-1,A2<>A1+1),"single",COUNTIF($A$1:A1,"<>")-SUM($B$1:B1)-COUNTIF($B$1:B1,"single"))) 

Thanks to Vaucluse for this tip.

Related


Published by aakai1056. Latest update on April 12, 2012 at 05:25 AM by aquarelle.
This document, titled "Excel - Find the number of occurrences of a number," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).