Count the number of columns used in Excel for a Specific text

[FWCRichardCoram]

Hi there,

I am hoping for a simple solution to a formula i have been trying to get right.
I am trying to sum the number of "Leave" Days off a Microsoft Shifts roster export. I think i am close using the COLUMNS and COUNTIF Function but i cant get it right.
The problem with the export is if a staff member has more than one consecutive days leave it merges the cells into one.
I am trying to simply count how many days leave they have had in a range.

Below is screen grab of my spreadsheet with COLUMN D being where i need the formula.
Cells F8:I8 are merged

=COUNTIF(COLUMN(E8:I8,"Leave"))???

Any help would be greatly appreciated

 

[Anthony47]

Few chances using stanfard functions, much better using a Customized one
1) Insert this code into a standard module of your vba project:

Function CountInRange(ByRef myRan As Range, ByVal myVal) As Long
Dim iCnt As Long, myC As Range
'
myVal = UCase(myVal)
For Each myC In myRan
    If UCase(myC.MergeArea.Cells(1, 1).Value) = myVal Then
        iCnt = iCnt + 1
    End If
Next myC
CountInRange = iCnt
End Function

2) Now if you go to your worksheet, you can use in D4 the formula

=CountInRange(E4:J4,"leave")

Copy down as needed

 

[FWCRichardCoram]

thank you, worked a treat!