Count if problems
 

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

Count if problems
 

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.

--
Biff
Microsoft Excel MVP


"mjlunt" wrote in message
...
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

Count if problems
 

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

"mjlunt" wrote:

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

Count if problems
 

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


"mjlunt" wrote:

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

Count if problems
 

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

--
__________________________________
HTH

Bob

"mjlunt" wrote in message
...
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

Count if problems
 

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

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
=COUNTIF(A1:A14,"*1*").

--
__________________________________
HTH

Bob

"mjlunt" wrote in message
...
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

Count if problems
 

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.....

"wd" wrote:

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


"mjlunt" wrote:

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

Count if problems
 

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" wrote in message
...
That'll miss cells which contain just the single entry 1 (if a single
entry has not been formatted as text).

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
=COUNTIF(A1:A14,"*1*").

--
__________________________________
HTH

Bob

"mjlunt" wrote in message
...
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

Count if problems
 

COUNTIF and SUMIF

They sure are loaded with nuance, especially COUNTIF.


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
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" wrote in message
...
That'll miss cells which contain just the single entry 1 (if a single
entry has not been formatted as text).

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
=COUNTIF(A1:A14,"*1*").

--
__________________________________
HTH

Bob

"mjlunt" wrote in message
...
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