L
Larry
i would like to count the number of times the number1 appears in
column L.
what is the proper syntax for this formula?
TIA
column L.
what is the proper syntax for this formula?
TIA
D
Don Guillett
look in HELP index for COUNTIF
C
Chip Pearson
Larry,
You can use the COUNTIF function to do this. E.g.,
=COUNTIF(L1:L100,1)
You can use the COUNTIF function to do this. E.g.,
=COUNTIF(L1:L100,1)
B
Bob Phillips
Hi Larry,
=SUM(LEN(L1:L1000)-LEN(SUBSTITUTE(L1:L1000,"1","")))
=SUM(LEN(L1:L1000)-LEN(SUBSTITUTE(L1:L1000,"1","")))
K
Ken Wright
Array entered CTRL+SHIFT+ENTER 
K
Ken Wright
Larry, just to clarify a couple of the reponses you have had, only your question was slightly
ambiguous in that you could interpret it two different ways:-
1) Chip has given you a solution that will count every cell that has 1 and only 1 in it. 11 in a
cell would not get counted as 2, in fact it would not get counted at all (Which may well in fact
be what you want)
2) Bob has give you a solution that will count every instance of the number 1 in the cells in your
range. 11 would be counted as 2 lots of 1, 111 would be counted as 3, 101214171 would be counted
as 5 instances.
Your call, but just be sure you really get what you want.
ambiguous in that you could interpret it two different ways:-
1) Chip has given you a solution that will count every cell that has 1 and only 1 in it. 11 in a
cell would not get counted as 2, in fact it would not get counted at all (Which may well in fact
be what you want)
2) Bob has give you a solution that will count every instance of the number 1 in the cells in your
range. 11 would be counted as 2 lots of 1, 111 would be counted as 3, 101214171 would be counted
as 5 instances.
Your call, but just be sure you really get what you want.