1

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.

Improve this question
1
  • Your problem is not quoting Requirement in the VBA. SeanC's answer below is correct. Commented Nov 29, 2018 at 7:27

4 Answers 4

Reset to default
1

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"")"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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).

Improve this answer

Comments

0

... 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"")"
Improve this answer

5 Comments

I tried a different way of building this formula, and while I did not get any errors, the sums were still zero (meaning that I am still comparing against a string, I think): Range("G" & Start(groups) - 1).Formula = "=COUNTIFS(E" & Start(groups) & ":E" & Finish(groups) & "," & "Requirement" & ",S" & Start(groups) & ":S" & Finish(groups) & ",""<>4"")"
John - I did exactly what you suggested... still zeros. Don't know what I'm doing wrong. Sorry I mis-read your initial post. When I look at the formula left in the cells, it still has quotes around the <>4. >:/
@aerogal, do you want the quotes aroudn the <>4 or not? your original post showed that there were quotes around it??? - If you want it WITHOUT the quotes, simply change the part in my formula above to ",<>4)"
I am not sure anymore :( I tried it that way, and I got an error. I've tried so many things to get this to work... I know I'm starting to sound like I'm giving up, and I'm sorry about that. I've tried so many permutations. The second condition is to test against a number (i.e., 4). The first condition is to test against a string (i.e., requirement). I even tried Range("G" & Start(groups) - 1).Formula = "=COUNT(IF((S" & Start(groups) & ":S" & Finish(groups) & ",""<>4""))"",E" & Start(groups) & ":E" & Finish(groups) & ")" -- of course I get an error with this too.
Thank you for taking the time to look at this. I appreciate the feedback.
0

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")
Improve this answer

2 Comments

Rick, I want to agree with you. The problem is that this statement used to be just a "countif" with the initial condition (comparing against requirement). And it really worked just fine. I have a spreadsheet full of hundreds of requirements and they are grouped into all these subgroups, each summed correctly within their respective groups. When I add the 2nd condition (to only count requirements that are not level 4) it blows this to pieces. I get zero sums. So frustrating. Now these requirements have 3 rows allocated to each, which is why the column is testing for "requirement" first.
Can you give me an example of your requirement data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.