{"id":66913,"date":"2006-07-13T08:02:00","date_gmt":"2006-07-13T08:02:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/07\/13\/how-can-i-add-a-countif-formula-to-an-excel-spreadsheet\/"},"modified":"2006-07-13T08:02:00","modified_gmt":"2006-07-13T08:02:00","slug":"how-can-i-add-a-countif-formula-to-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-add-a-countif-formula-to-an-excel-spreadsheet\/","title":{"rendered":"How Can I Add a COUNTIF Formula to an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I add a COUNTIF formula to an Excel spreadsheet?

— MF<\/P>\"Spacer\"\"Hey,\"TechNet<\/A> \n

Hey, MF. Thanks for reminding us: yes, we are<\/I> getting closer and closer to the actual 500<\/B>th<\/SUP> Hey, Scripting Guy! Celebration<\/B><\/A>. <\/P>\n\n<\/THEAD>\n\n\n
\n

Note.<\/B> There was a bunch of stuff here about the celebration, but it\u2019s over now so we took it out. That should explain the small gap in content here. Any other gaps in content are purely coincidental.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Anyway, thanks again, MF; we almost forgot to mention all that. As a token of our appreciation, we\u2019d like to give you a little something in return. Obviously you would never take our money \u2026 hey, how about this: how about a script that adds a COUNTIF formula to an Excel spreadsheet? It\u2019s not much, but, then again, it\u2019s the thought that counts. Right?<\/P>\n

As you noted in your email, MF, you tried adding a COUNTIF formula (a formula that simply counts the number of cells in a range whose value begins with the letters NK<\/I>) using this line of code:<\/P>

objExcel.Cells(6, 1).Formula = “=COUNTIF(A1:A4,’NK*’)”\n<\/PRE>\n
That looks pretty good, but – as you found out – it doesn\u2019t work. You\u2019d like to know two things: why<\/I> doesn\u2019t this work, and what can you do to make it work. Can we help you with those questions? Hey, does a bear swim in the woods?<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Actually, no, we have no idea<\/I> what we would do if someone told us that, from now on, these columns had to start making sense.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Let\u2019s start off by explaining why your code doesn\u2019t work. To begin with, take a look at the COUNTIF formula as typed directly into Excel:<\/P>\"Hey, \n

If you look closely, you\u2019ll notice that the criteria (NK) is surrounded by double quotes: “NK”<\/B>. Are those double quotes important? You bet they are; if we replace the double quotes with single quotes we no longer have a formula:<\/P>\"Hey, \n

As you found out.<\/P>\n
Of course, when you first sat down to write your script you probably tried inserting double quotes, most likely by using code similar to this:<\/P>
objExcel.Cells(6, 1).Formula = “=COUNTIF(A1:A4,”NK*”)”\n<\/PRE>\n
There\u2019s probably no need to tell you that that\u2019s going to generate an \u201cExpected end of statement\u201d error. Why? Well, the double quote mark is used to indicate the beginning and the end of string values. Because of that, VBScript thinks that your string value is this:<\/P>
“=COUNTIF(A1:A4,”\n<\/PRE>\n
That\u2019s fine; it\u2019s perfectly valid VBScript code. However, that valid string value is immediately followed by this:<\/P>
NK*”)”\n<\/PRE>\n
Does that look like gibberish to you? Well, unfortunately, it looks like gibberish to VBScript as well; that\u2019s why the script blows up. One of the many immutable laws of the universe is this: you can\u2019t embed double quotes inside double quotes. <\/P>\n
At least not if you want your script to actually run.<\/P>\n
To your credit, though, we have to admit that it wasn\u2019t a bad idea to try replacing the double quotes in the formula with single quotes; sometimes (especially when you\u2019re working with HTML tags) you can get away with that. For the most part, though, that won\u2019t work: if something expects to see double quotes, then you have no choice but to give it double quotes.<\/P>\n
But didn\u2019t we just that you can\u2019t<\/I> embed double quotes inside double quotes? Probably; that sounds like something we\u2019d say. But that\u2019s OK; we aren\u2019t going to embed double quotes inside double quotes. Instead, we\u2019re going to use a little workaround like this:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objExcel.Cells(1, 1).Value = “nkabc”\nobjExcel.Cells(2, 1).Value = “abcnk”\nobjExcel.Cells(3, 1).Value = “abnkc”\nobjExcel.Cells(4, 1).Value = “nk”<\/p>\n
strFormula = “=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”\nobjExcel.Cells(6, 1).Formula = strFormula\n<\/PRE>\n
Two quick notes before we explain how this script works. First, there are at least two ways to get around the fact that you can\u2019t embed double quotes inside double quotes. We\u2019ve chosen the method that we believe is easier for most people. If you prefer a different method, that\u2019s fine.<\/P>\n
Second, we actually added an extra step to our script. Where? Right near the end, where we stored our COUNTIF formula in a variable and then assigned that variable to a specific cell in the spreadsheet. We could have saved ourselves a step by assigning the formula directly to the cell, without ever storing that formula in a variable. We didn\u2019t use that shortcut simply because we thought it was a little easier for people to read and understand the script if we broke that task into two steps. <\/P>\n
But enough about that. We\u2019ve now reached the point where we explain how this script works, something we\u2019ll do by first telling you that we aren\u2019t going to explain how this script works. Or at least not all of it. As it turns out, 80% of the script has nothing to do with inserting a formula; instead, it\u2019s code that simply creates an Excel spreadsheet and adds some test values to four of the cells. We won\u2019t explain those lines of code in any detail; if you aren\u2019t familiar with writing scripts that use Microsoft Excel then you might want to take a look at the articles in the Office Space Archive<\/B><\/A>. (In fact, we even have one article in there that talks about inserting formulas<\/B><\/A> into an Excel spreadsheet.)<\/P>\n
To make a long story short, we start off by creating a visible instance of Microsoft Excel, and then creating a new workbook. After binding to sheet 1 in that workbook we then use these four lines of code to add four values (two of which start with the letters NK<\/I> and two of which don\u2019t) into cells A1 through A4.<\/P>
objExcel.Cells(1, 1).Value = “nkabc”\nobjExcel.Cells(2, 1).Value = “abcnk”\nobjExcel.Cells(3, 1).Value = “abnkc”\nobjExcel.Cells(4, 1).Value = “nk”\n<\/PRE>\n
That leaves us with only two lines of code to worry about:<\/P>
strFormula = “=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”\nobjExcel.Cells(6, 1).Formula = strFormula\n<\/PRE>\n
The first line is the important one: that\u2019s the line where we construct the COUNTIF formula. To do that we need to build the formula in pieces. As you might recall, the finished product needs to look like this:<\/P>
“=COUNTIF(A1:A4,”NK*”)”\n<\/PRE>\n
To get to that point, we start with this string:<\/P>
“=COUNTIF(A1:A4,”\n<\/PRE>\n
Note that this is a valid string: it begins and ends with double quote marks. We then need to insert a double quote mark; to do that we use the Chr<\/B> function, which converts an ASCII value to an actual character. As it turns out, the ASCII value<\/B><\/A> for the double quote mark is 34; that means that this piece of code takes the beginning portion of the string and then adds a double quote mark at the end:<\/P>
“=COUNTIF(A1:A4,” & Chr(34)\n<\/PRE>\n
Got that? Good. Next up: NK*. No problem: we just put those characters in a string and add that<\/I> to the formula:<\/P>
“=COUNTIF(A1:A4,” & Chr(34) & “NK*”\n<\/PRE>\n
Make sense? Now we need a second double quote mark, which means it\u2019s time for another Chr(34):<\/P>
“=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34)\n<\/PRE>\n
All that\u2019s left now is the closing parenthesis, which we can add as a little string value all its own:<\/P>
“=COUNTIF(A1:A4,” & Chr(34) & “NK*” & Chr(34) & “)”\n<\/PRE>\n
Admittedly, this can be a bit bewildering if you haven\u2019t done much string concatenation. If that\u2019s the case, then you can do two things that might help. First, take a look at the relevant section in the Microsoft Windows 2000 Scripting Guide<\/B><\/A>. Second, think of this in terms of an addition problem:<\/P>
“=COUNTIF(A1:A4,” \n+                Chr(34) \n+                  “NK*” \n+                Chr(34) \n+                    “)”\n________________________<\/p>\n
  “=COUNTIF(A1:A4,”NK*”)”\n<\/PRE>\n
To turn this into a line of code just replace the plus signs with ampersands. <\/P>\n
Fortunately all that\u2019s left now is to assign the formula to cell A6:<\/P>
objExcel.Cells(6, 1).Formula = strFormula\n<\/PRE>\n
That should do it, MF. Again, thanks for your help. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. More stuff about the celebration<\/B><\/A> was here. It\u2019s gone now.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I add a COUNTIF formula to an Excel spreadsheet?— MF Hey, MF. Thanks for reminding us: yes, we are getting closer and closer to the actual 500th Hey, Scripting Guy! Celebration. Note. There was a bunch of stuff here about the celebration, but it\u2019s over now so we took it […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-66913","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I add a COUNTIF formula to an Excel spreadsheet?— MF Hey, MF. Thanks for reminding us: yes, we are getting closer and closer to the actual 500th Hey, Scripting Guy! Celebration. Note. There was a bunch of stuff here about the celebration, but it\u2019s over now so we took it […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66913"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66913\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}