Countif With Multiple OR Criteria

Question

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, or
• PRE

Answer 1

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""}))")

Answer 2

The quickest way would just be to do 2 COUNTIFs:

iVal = Application.WorksheetFunction.CountIf(Range("A:A"), "SAL") + Application.WorksheetFunction.CountIf(Range("A:A"), "PRE")

Answer 3

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)