I need to count the number of cells in a column which contain a specific value. I am currently using the following code:
iVal = Application.WorksheetFunction.COUNTIF(Range("A:A"), "SAL")
However, I now need to count how many cells contain either
SAL, orPREThe quickest way would just be to do 2 COUNTIFs:
iVal = Application.WorksheetFunction.CountIf(Range("A:A"), "SAL") + Application.WorksheetFunction.CountIf(Range("A:A"), "PRE")
You could use the VBA equivalent of a standard COUNTIF formula (which can take more than one argument). This can be expanded as required
Standard Formula
=SUM(COUNTIF(A:A, {"SAL","PRE"}))
VBA Equivalent
MsgBox Evaluate("Sum(COUNTIF(A:A,{""PRE"",""SAL""}))")
Why dont you use the CountIfs function?
Example:
Application.CountIfs(CR1_range, V_1, CR2_range, V_2, CR3_range, V_3, CR4_range, V_4)
Where CR1_range is obviously your range and V_1 is your variable
Edit: Ooops! Just seen that it must be a OR statement, my answer would only work for an AND statement (didn't delete for reference incase anyone is interested)
Following worked well for me: -
Dim val1 as string, val2 as string
val1 = "value1"
val2 = "value2"
count = Application.WorksheetFunction.CountIfs(ThisWorkbook.Names("firstname").RefersToRange, val1 , ThisWorkbook.Names("secondname").RefersToRange, val2)
This webpage helps regarding Named Ranges in Excel.
