# Countif - Counting values within a cell

#### MrPink1986

##### Active Member
Hi,

I have a spreadsheet which details multiple lines of data in one column - I would like to use a countif formula (or something similar) to count the values within this column. Each cell can have multiple lines of data and multiple values. Based on the mock data below I would like to count the values in column B based on the column A.

Each value in column A should be equal to 4.
I hope this is clear...............

I tried to utilise the countif formula but this is not returning the required value back.

 Test Test Test1 Test2 Test Test1 Test2 Test1 Test Test1 Test2 Test2 Test Test1 Test2

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Candyman8019

##### Board Regular
Try something like this:
=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+(LEN(A2:A4)>1))

#### MrPink1986

##### Active Member
Hi
Try something like this:
=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+(LEN(A2:A4)>1))
Thanks for the response however when I run this formula I get a value of 2 back for each value. I would expect a value of 4 based on the data in B. I am looking to count how many times the value in column A is present in column B.

#### Fluff

##### MrExcel MVP, Moderator
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

+Fluff v2.xlsm
ABC
1
2TestTest Test1 Test2 Test Test1 Test24
3Test1Test Test1 Test24
4Test2Test Test1 Test24
Main
Cell Formulas
RangeFormula
C2:C4C2=SUM(LEN(\$B\$2:\$B\$4&CHAR(10))-LEN(SUBSTITUTE(\$B\$2:\$B\$4&CHAR(10),A2&CHAR(10),"")))/LEN(A2&CHAR(10))

Replies
0
Views
48
Replies
4
Views
69
Replies
3
Views
135
Replies
4
Views
100
Replies
16
Views
169

1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back