Countif - Counting values within a cell

MrPink1986

Active Member
Joined
May 1, 2012
Messages
252
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.

TestTest
Test1
Test2
Test
Test1
Test2
Test1Test
Test1
Test2
Test2Test
Test1
Test2
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Try something like this:
=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+(LEN(A2:A4)>1))
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
252
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
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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
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))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top