How to count merged cells containing text

TimetablingABC

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I need to count cells both merged and unmerged (mainly merged vertically in columns) on a text string in the cell (text string may be long but I just need to search for a name out of the text). The cells are also conditionally formatted different colours, don’t know if this caused a problem? Does anyone know if this is possible please?

Also, the workbook is a timetable and each cell represents 15 minute slots (6 merged cells represent 1hour 30 mins) so I will have to do some math after that, which I’m presuming will be easier separately using the count to work out how much time a name has allocated, 15mins x count result?

Please help, my brain has gone to spaghetti trying to figure out how to count merged cells, I’m abit of a novice!?

PS. The worksheet is inherited from someone else so I am not responsible for the extensive use of merged cells 😁
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,689
Office Version
  1. 365
Platform
  1. Windows
Sometimes when you inherit someone else's disaster, it is less time consuming to start again that to try and fix what you've been given to work with.

Staring off with the simplest method then it can be expanded if needed. Assuming that your merged cells are in column A, you could use something like

=COUNTIF(A:A,"*cat*")

to count the cells that contain the word "cat" anywhere in the string. Note that this will not differentiate between the word 'cat' or longer words that contain 'cat'.

When you count merged cells (or use the content of a merged cell as a reference to something else) it is important to remember than it only counts as 1, regardless of how many rows / columns are merged. The content defaults to the top left cell of the merged selection, with all of the other cells being empty (this default can not be changed).
 

TimetablingABC

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Sometimes when you inherit someone else's disaster, it is less time consuming to start again that to try and fix what you've been given to work with.

Staring off with the simplest method then it can be expanded if needed. Assuming that your merged cells are in column A, you could use something like

=COUNTIF(A:A,"*cat*")

to count the cells that contain the word "cat" anywhere in the string. Note that this will not differentiate between the word 'cat' or longer words that contain 'cat'.

When you count merged cells (or use the content of a merged cell as a reference to something else) it is important to remember than it only counts as 1, regardless of how many rows / columns are merged. The content defaults to the top left cell of the merged selection, with all of the other cells being empty (this default can not be changed).
Hi, thanks for your reply. Unfortunately I need a way to count the merged cells as their multiple of unmerged (eg. If 6 unmerged cells were merged into 1 I need a return count of 6, as they represent 6 occurrences of a time slot I need to add up, but were merged so visually a lot of info could go into that block).

When I have more time I will look at an alternative to using merged cells, but for now that’s not practical time wise for me. I may just have to resort to counting them manually? I’ve looked into VBA but am struggling as I’m somewhat of a novice n it’s not working.

Thank you for your suggestion 👍🏻
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What strings are calculated from the cells?
I mean: How do you know what text you are looking for?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,689
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

eg. If 6 unmerged cells were merged into 1 I need a return count of 6, as they represent 6 occurrences of a time slot I need to add up
There is no formula that would do that, the only way it might be possible is by comparison to adjacent data if there is any way to identify the end of the merged cells from that.

With vba it would be easier, unlike a formula, vba does appear to count each cell that is merged rather than just the first one. See if this udf does as needed.
Enter the code into the editor, then use the formula like =countmerge(A2:A20,"cat") in your sheet as normal (no wildcard characters with this one).

VBA Code:
Public Function countmerge(rng As Range, criteria As Variant) As Long
Dim tmp As Long, c As Range
For Each c In rng
    If InStr(criteria, c.Value) Then tmp = tmp + 1
Next
countmerge = tmp
End Function
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How about this?

VBA Code:
'Can be used directly from workbook: =CountCells(Range:Range,"String")
Public Function CountCells(SrcRNG As Range, SrcSTR As String) As Long ' Count ALL cells that contain String(SrcSTR) , including All cells that is Merged together
Dim Cell As Range, Tmp As Long
SrcSTR = "*" & SrcSTR & "*" ' Add * for search string
    For Each Cell In SrcRNG
        If Cell.MergeArea(1).Value Like SrcSTR Then Tmp = Tmp + 1
    Next
CountCells = Tmp
End Function

Sub test_CountCells() ' example of using with VBA
Dim SrcRNG As Range, SrcSTR As String, t As Long
Set SrcRNG = Selection ' Range to search (now it's activesheet selected range
SrcSTR = "Here string to search" ' this is the string to search
t = CountCells(SrcRNG, SrcSTR)
Debug.Print t & "/" & SrcRNG.Cells.Count ' print (cells with match)/(cells in range) -just for check
End Sub
 

TimetablingABC

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

What strings are calculated from the cells?
I mean: How do you know what text you are looking for?
I was going to have a table/list of names n then I was hoping for a solution to count how many cells/15 minute blocks are attributed to that name eventually ending up with a list of names and how many hours each name is attributed throughout a timetable. However, the problem I have is most of the blocks of cells have been merged (eg. 3 cells for 45mins, 8 cells for 2 hours) and there are many names across a fortnightly time period, and slots can often change hence my desire to automate the count so I can see at any point how many hours a name has
 

TimetablingABC

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
There is no formula that would do that, the only way it might be possible is by comparison to adjacent data if there is any way to identify the end of the merged cells from that.

With vba it would be easier, unlike a formula, vba does appear to count each cell that is merged rather than just the first one. See if this udf does as needed.
Enter the code into the editor, then use the formula like =countmerge(A2:A20,"cat") in your sheet as normal (no wildcard characters with this one).

VBA Code:
Public Function countmerge(rng As Range, criteria As Variant) As Long
Dim tmp As Long, c As Range
For Each c In rng
    If InStr(criteria, c.Value) Then tmp = tmp + 1
Next
countmerge = tmp
End Function
Great, I’ll have a look n give it a try cheers, really appreciate it!
 

TimetablingABC

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
How about this?

VBA Code:
'Can be used directly from workbook: =CountCells(Range:Range,"String")
Public Function CountCells(SrcRNG As Range, SrcSTR As String) As Long ' Count ALL cells that contain String(SrcSTR) , including All cells that is Merged together
Dim Cell As Range, Tmp As Long
SrcSTR = "*" & SrcSTR & "*" ' Add * for search string
    For Each Cell In SrcRNG
        If Cell.MergeArea(1).Value Like SrcSTR Then Tmp = Tmp + 1
    Next
CountCells = Tmp
End Function

Sub test_CountCells() ' example of using with VBA
Dim SrcRNG As Range, SrcSTR As String, t As Long
Set SrcRNG = Selection ' Range to search (now it's activesheet selected range
SrcSTR = "Here string to search" ' this is the string to search
t = CountCells(SrcRNG, SrcSTR)
Debug.Print t & "/" & SrcRNG.Cells.Count ' print (cells with match)/(cells in range) -just for check
End Sub
That's great, I’ll give it a go and see, thank you really appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,244
Messages
5,641,058
Members
417,190
Latest member
Sdwd76

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