CODEWITHSUNDEEP
excelvba
Aerogal31
Aerogal31

Reputation: 85

Creating a VBA formula using CountIF

I am creating a formula to count based upon 2 conditions. My logic is wrong - again.

When I (manually) enter enter code the summation formula (COUNTIFS) into correct cell itself, it sums correctly:

COUNTIFS(E4:E1362,"Requirement",S4:S1362, "<>4")

When I execute the following code line, I do not get any errors, but instead, all the sums are zero.

Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & "," & "Requirement" & ",S" & Start(groups) & ":S" & Finish(groups) & "," & Chr(34) & "<>4" & Chr(34) & ")"

I realized that the COUNTIFS was comparing the value in column S to the string "<>4". And none of the cells contain that string. This is why all my values were zero. I do not want the comparison to be against that string. I want the comparison to be column S value NOT equal to 4.

So, I changed the line to (and variations to get it to work):

Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & "," & "Requirement" & ",S" & Start(groups) & ":S" & Finish(groups) & "," & Chr(34) & "<>" & Chr(34) & "4)"

So, I am at a loss. (1) I manually enter the formula and it works. (2) I build it in VBA, and it does not sum correctly. (3) I look up how to do build the formula correctly, and I get errors time and time again. It seems like the only way to get this to work is to keep the quotes within quotes, but I don't want to compare against the string.

Does this make sense? I'm not liking my worksheet anymore. It is no longer any fun at all. :(

Thank you so much.

Upvotes: 1

Views: 12945

Answers (4)

Serafin William
Serafin William

Reputation: 194

Well, One of the problem I found there was quite simple, instead using "," you must use ";". It depends on the version you used, some using "," and some others using ";". you can just try it, I hope it solved your problem.

and second one "Requirement" there, I think you should use double quote instead.

and for your case To meet This condition:

COUNTIFS(E4:E1362,"Requirement",S4:S1362, "<>4")

Use This:

 "=COUNTIF(E" & Start(groups) & ":" & "E" & Finish(groups) & "," & """Requirement""" & "," & "S" & Start(groups) & ":" & "S" & Finish(groups) & "," & """<>4"")"

I think it Should Work (if I don't miss any Quotes thought).

Upvotes: 1

Rick
Rick

Reputation: 1073

After running a quick test with your initial code

Range("G1").Formula = "=COUNTIFS(E1" & ":E20" & "," & "Requirement" & ",S1" & ":S20" & "," & Chr(34) & "<>4" & Chr(34) & ")"

Modified slightly to account for your groups variable cell G1 ends up with the following formula:

=COUNTIFS(E1:E20,requirement,S1:S20,"<>4")

which when tested works absolutely fine for your second if statement S1:S20,"<>4". I would guess that the problem lies with your first statement. What is in your requirement range and what data are you trying to match with.

I set up the requirement named range to be cell D5, I entered a 5 in there and the formula would increment each time I added an extra 5 to the range E1:E20. I then started typing 4's into the range S1:s20 and this decreased my count.

edit The last thing that I can suggest is to add in a helper column in column F that evaluates row to see if it matches the requirement condition, then it would be a simple matter of making your formula:

=COUNTIFS(F1:F20,TRUE,S1:S20,"<>4")

Upvotes: 0

SeanC
SeanC

Reputation: 15923

I think all the Chr(34) are getting in the way of you viewing your formula correctly. try using "" to make the quote for the formula.

.Formula = "=COUNTIFS(E4:E1362,""Requirement"",S4:S1362,""<>4"")"

with your Start and Finish functions, that would change the formula to:

.Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & _
    ",""Requirement"",S" & Start(groups) & ":S" & Finish(groups) & ",""<>4"")"

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

... Try removing the chr(34) and the extra quotations - It should look exactly like the "Requirement" criteria:

Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & ",""Requirement"",S" & Start(groups) & ":S" & Finish(groups) & ",""<>4"")"

Upvotes: 0

Related Questions