Search code examples
vbaexcelexcel-2013worksheet-function

VBA CountIFS With Multiple Criteria

I have a VBA procedure that scans a worksheet and produces counts where cell C is not equal to certain colors, but column I is set to one value. This is the procedure:

FormulaR1C1 = "=COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,""<>Red"",
Sheet1!C,""<>Blue"",Sheet1!C,""<>Green"",Sheet1!C,""<>Black"",
Sheet1!C,""<>Purple"",Sheet1!C,""<>White"",
Sheet1!C[6],""Temp"")"

Conditions have changed and I need to add in another criteria for count condition so I thought it would be a quick fix of adding in a comma and the criteria to the end like this

FormulaR1C1 = "=COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,""<>Red"",
Sheet1!C,""<>Blue"",Sheet1!C,""<>Green"",Sheet1!C,""<>Black"",
Sheet1!C,""<>Purple"",Sheet1!C,""<>White"",
Sheet1!C[6],""Temp"",Sheet1!C[6],""Perm"")"

However - now this always returns 0. What is the correct way to add in a secondary condition in VBA to a CountIFS()

EDIT
That second parameter I want to add, should be an "OR" condition as well, so Sheet1!C[6] = Temp OR Perm

EDIT 2
I tried to edit my syntax like this

FormulaR1C1 = "=COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,""<>Red"",
Sheet1!C,""<>Blue"",Sheet1!C,""<>Green"",Sheet1!C,""<>Black"",
Sheet1!C,""<>Purple"",Sheet1!C,""<>White"",
Sheet1!C[6],""Temp"")"
+
"COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,""<>Red"",
Sheet1!C,""<>Blue"",Sheet1!C,""<>Green"",Sheet1!C,""<>Black"",
Sheet1!C,""<>Purple"",Sheet1!C,""<>White"",
Sheet1!C[6],""Perm"")"

but this gives me an error of

application defiend or object defined error

Solution 
  • Dim f
f = "=COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,'<>Red'," & _
    "Sheet1!C,'<>Blue',Sheet1!C,'<>Green',Sheet1!C,'<>Black'," & _
    "Sheet1!C,'<>Purple',Sheet1!C,'<>White'," & _
    "Sheet1!C[6],'Temp') + COUNTIFS(Sheet1!C[-2],RC[-2],Sheet1!C,'<>Red'," & _
    "Sheet1!C,'<>Blue',Sheet1!C,'<>Green',Sheet1!C,'<>Black'," & _
    "Sheet1!C,'<>Purple',Sheet1!C,'<>White',Sheet1!C[6],'Perm')"


FormulaR1C1 = Replace(f, "'", """")