Countif - Counting values within a cell

[MrPink1986]

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

 

[Candyman8019]

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

 

[MrPink1986]

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]

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’)

How about

+Fluff v2.xlsm
	A	B	C
1
2	Test	Test Test1 Test2 Test Test1 Test2	4
3	Test1	Test Test1 Test2	4
4	Test2	Test Test1 Test2	4
Main

Cell Formulas
Range	Formula
C2:C4	C2	=SUM(LEN($B$2:$B$4&CHAR(10))-LEN(SUBSTITUTE($B$2:$B$4&CHAR(10),A2&CHAR(10),"")))/LEN(A2&CHAR(10))