# 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

#1
July 10, 2011 at 09:15:25
 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 2Another way of doing it:Enter your search number in cell C1 and use the formula this way: =COUNTIF(A1:A3,"*"&C1&"*")MIKEhttp://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?MIKEhttp://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. A1) 1,2 <--Pen and Paper2) 1 <--Pen3) 2 <--Paper4) 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.MIKEhttp://www.skeptic.com/

Report •

#6
July 13, 2011 at 07:42:59
 Try this:With your data in column A: A1) 1,1,32) 4,5,63) 7,1,9In cell B1 enter the formula: =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"1","")))It should return 2Another way of doing it:Enter your search number in cell C1and use the formula this way: =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))MIKEhttp://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! XDThanks x10000000

Report •