Excel - Find the number of occurrences of a number

July 2016


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.

See also :

This document entitled « Excel - Find the number of occurrences of a number » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.