Category Archives: Microsoft Office

Using COUNTIF In Excel

Why would you use the COUNTIF function? This is a question that you may wonder at times. First of all, you could use it to figure out how many cells in a range match the specified criteria. You could also use it to determine whether or not a value in one cell is found in a range of other cells. The following is an example table which will be used to accomplish both of these tasks:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West
3 Beth Coope 24 Elizabeth Coope
4 Dan Yoder 17 Vladimir Yoder
5 Susie Queue 19 Beth Coope
6 Matthew Franklin 18 Jeffrey Franklin
7 Vladimir Yoder 25 Victoria Yoder
8
9 Under 18:
10 At least 18:

As you can see, cells D2 to D7 should indicate whether or not the patient is also a relative. To do this, do the following:

  1. Enter =COUNTIF($A$2:$A$7,$C2)>0 into cell D2.
  2. Select cell D2 and use the CTRL + C key combination to copy the cell.
  3. Select cells D3 to D7 and use the CTRL + V key combination to paste the relative formula into these cells.

Now the table should look like the following:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18:
10 At least 18:

This formula starts off by counting the number of times the value in the same row and in column C was found in the range of A2 to A7. Next, the cell determines whether or not the count of these matches is greater than 0. If the count is greater than zero, the cell evaluates to TRUE. If the count is not greater than zero, the cell evaluates to FALSE.

Now it is time to figure out the count those under 18, compared to those who are at least 18:

  1. Enter =COUNTIF($B$2:$B$7,"<18") into cell B9.
  2. Enter =COUNTIF($B$2:$B$7,">=18") into cell B10.

Now the table should look like the following:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18: 1
10 At least 18: 5

The formula in cell B9 simply counts how many cells in the range B2 to B7 are less than 18. The formula in cell B10 counts how many cells in the range B2 to B7 are greater than or equal to 18.

Excel – Batch Convert XLS To CSV

Update!!!

A newer HTML application (HTA) has just been created which accomplishes the same thing but in a more customizable manner. Click here to read the post.

Original Post

A few days ago, someone asked me to help them convert a large number of XLS files to CSV. Since, while working for Bristol-Myers Squibb, I became very familiar with writing macros and JScripts to work with Excel, I decided to write a JScript to accomplish the task. Basically, all you have to do is put the script inside of the same folder as all of the Excel files and double-click it to run it. The script uses Excel to open all of the files and convert each worksheet of each XLS file to a CSV. The naming convention for the CSV files is as follows:
{file_name}-{worksheet_name}.csv

Here is the script:
Excel – XLS To CSV Converter

If you examine the script, you will notice that it uses jPaq for user interaction.  All of the code, except for the jPaq because it is the minifieid version, is pretty well documented.  You may do whatever you want to the script. All I ask is that, if you re-use the code, you give credit where it is due. :)

Currency In A Mail Merge

While verifying the validity of a mail merge, one of my coworkers ran into the issue of extracting a currency value from a spreadsheet and correctly formatting it in Microsoft Word. The issue stemmed from using this numeric picture field switch which is suggested by Microsoft:
\# $#,###.00

If you have a speadsheet full of large numbers, the above code will probably work. On the other hand, if you have a one or two digit number, a few spaces will exist between the dollar sign and the number. Perhaps this is desirable to people at Microsoft, but in many other places, this isn’t what people want. Instead, after doing a little research and trial runs, I found that this numeric picture field switch will display currency with the leading spaces for small numbers:
\# $,0.00