Total all occurences of value in a range

[Human_doing]

Hi all,

The below code provides the number of cells where the contained date falls within two date ranges and loops this process across every worksheet in a workbook.

Can anyone please provide the code to total the instances of an exact match rather than between two dates, i.e. the number of times that the word 'excel' comes up in a range?

Thanks

Sub Looper()
'Total the number of occurences using formula
Dim Sh As Worksheet
Dim LR As Long
For Each Sh In ActiveWorkbook.Worksheets
With Sh
LR = .Range("E" & .Rows.Count).End(xlUp).Row
 
.Range("I2").Formula = "=SUMPRODUCT((E2:E" & LR & ">MonthSheet!B2)*(E2:E" & LR & "<MONTHSHEET!C2))"< p>  
End With
 
Next Sh
 
End Sub

 

[GlennUK]

Do you mean the COUNTIF function?

 

[Human_doing]

Hi, yes perhaps COUNTIF would be better for this however the SUMPRODUCT has got the job done for the first part of what I needed, I'm not sure which would be best for this?

Thanks

 

[GlennUK]

If you can do it with COUNTIF use that.