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

Here is one way: With your data in column A:

A 1) 1,2,3 2) 4,5,6 3) 7,1,9In 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

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

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

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

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

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

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

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History