VBA CountIFS With Multiple Criteria

Question

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

Inside the `COUNTIFS` everything is an AND... for the OR just sum your `COUNTIFS` like `=COUNTIFS(...Temp...)+COUNTIFS(...Perm...)`. Unlike your case keep in mind: if multiple parts of the OR can be true you need to subtract that cases where this happens or it will be counted multiple times. — Dirk Reichel, Mar 18 '17 at 22:54
@DirkReichel - see my edit. I must just be off with the syntax. I tried to add a + in there and am getting an error....And thanks for the words of advice. There is only the possibility for ONE answer to be in the cell, I just need to check for both. — BellHopByDayAmetuerCoderByNigh, Mar 18 '17 at 23:01
Your question is strikingly similar to this: https://stackoverflow.com/questions/42880532/countifs-always-returning-a-0. Are you guys working on the same project perhaps? Anyway - the answer to other question was that it was just a typo. — Robin Mackenzie, Mar 19 '17 at 00:54
@RobinMackenzie - unfortunately in my instance there is no typo. Well there couldbe in the syntax, I just don't see it. No not the same project. — BellHopByDayAmetuerCoderByNigh, Mar 19 '17 at 01:19

score 1 · Accepted Answer · answered Mar 19 '17 at 02:55

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, "'", """")

VBA CountIFS With Multiple Criteria

1 Answers1