Solved COUNTIF with multiple numbers in 1 cell

July 9, 2011 at 23:11:59
Specs: Windows 7
I have a countif formula in B1 that finds the number "1" in cells A1:A3.
I want to be able to enter lots of numbers in each cell and still have it return results.

At the moment, when I type 1,2,3 in A1, B1 shows 0 results, I have to write only one number in the cell for it to work.

Can someone please tell me how I can have 1,2,3 in A1 and have B1 find the value 1 in it?
Thanks...


See More: COUNTIF with multiple numbers in 1 cell

Report •


#1
July 10, 2011 at 09:15:25
✔ Best Answer
Here is one way:

With your data in column A:

     A
1) 1,2,3
2) 4,5,6
3) 7,1,9

In cell B1 enter the formula: =COUNTIF(A1:A3,"*1*")

It should return 2

Another way of doing it:

Enter your search number in cell C1
and use the formula this way: =COUNTIF(A1:A3,"*"&C1&"*")

MIKE

http://www.skeptic.com/


Report •

#2
July 12, 2011 at 00:40:24
Thanks a heap but is there a way to do it without having to enter the data like "1,2,3" in every cell...

For example, I wanted A3 to contain just "1" and not "1,0,0" but if I don't enter a comma it doesn't work... It needs to continue with that format.
If there is no way around this that's fine, just thought I should ask :)
Thanks again. XD


Report •

#3
July 12, 2011 at 12:15:14
In your original post you said:

how I can have 1,2,3 in A1 and have B1 find the value 1 in it?

Now you say you have just a single digit in the cell.

Just exactly what is it you are doing?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 12, 2011 at 22:11:15
I am listing items a person loans in column A with the item reference number (Pen=1 Paper=2).
If someone loans paper and a pen then I enter 1,2 to show items 1 and 2 are on loan.
I need to be able to count how many of each item are on loan and show it column B.
Some people loan 1 item, some 2, some 3, etc.
Understand? Sorry for the bad explanation :/
Eg.

A
1) 1,2 <--Pen and Paper
2) 1 <--Pen
3) 2 <--Paper
4) 1,1,2 <--2 Pens and 1 Paper


Report •

#5
July 13, 2011 at 07:35:18
One of the problems is that we are switching from TEXT to NUMBERS,

1,2,3 is seen as TEXT while just a single digit is a NUMBER.

I'll work on it.

BUT, for a single digit just add the comma, so 1 becomes 1,

Why don't you separate each item into it's own column,
which can be three characters wide?
Then doing a =COUNTIF() becomes very easy.

MIKE

http://www.skeptic.com/


Report •

#6
July 13, 2011 at 07:42:59
Try this:

With your data in column A:

A
1) 1,1,3
2) 4,5,6
3) 7,1,9


In cell B1 enter the formula: =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"1","")))

It should return 2

Another way of doing it:

Enter your search number in cell C1
and use the formula this way: =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))

MIKE

http://www.skeptic.com/


Report •

#7
August 7, 2011 at 19:21:18
Hey yeah that's working great thanks... I had to modify it so it detects a range and not only one cell but yeah it works!
People were saying what I wanted to do was designed for a RDBMS not a spreadsheet but I got it working so :P to them! XD

Thanks x10000000


Report •


Ask Question