Count if problems

[mjlunt]

I am trying to count the number of cells that contain the number 1 within the
data below. The formula that I am using is =COUNTIF(A1:A14,"1"). With this
formula the value returned to me is 2 however it should be 5. Please Help!!


2,3
2
2
1,2,3
3
2
2,3
1,2
2
1,2
3
1
1
2

 

[T. Valko]

Based on your posted sample where the numbers are only 1, 2 or 3.

=SUMPRODUCT(LEN(A2:A15)-LEN(SUBSTITUTE(A2:A15,1,"")))

Note that this won't work if you want to count 1s and have numbers like: 10,
11, 21, 101, etc.

 

[Stephen Lloyd]

If cells with multiple numbers always begin with the lowest number then you
can use the following:
=SUMPRODUCT(--(LEFT(F23:F36,1)="1"))

 

[wd]

Your formula should state: =COUNTIF(A1:A14,2) then your answer will be 5.

 

[Bob Phillips]

=COUNTIF(A1:A14,"*1*").

 

[T. Valko]

That'll miss cells which contain just the single entry 1 (if a single entry
has not been formatted as text).

 

[daddylonglegs]

This formula will count 1s

=SUMPRODUCT(--ISNUMBER(FIND("1,",A1:A14&",")))

It can be easily changed to count 2 or 12 without counting the 2 in 12.....

 

[Peo Sjoblom]

COUNTIF and SUMIF are crappy functions,

they count the below regardless if text or numeric and
if you remove the quotations they do the same but use
a wildcard and it looks for text only

COUNTIF(A1:A10,"1")



--


Regards,


Peo Sjoblom

 

[T. Valko]

COUNTIF and SUMIF

They sure are loaded with nuance, especially COUNTIF.