Excel: COUNTA, COUNTBLANK

Whereas COUNT counts only the cells that have numbers in them, COUNTA and COUNTBLANK are a little more loose with the definition of what they count. They count all cells and report on the information.

COUNTA

COUNTA counts cells that are not blank.

You might use blank spaces to create a view that separates certain types of data so that it’s easier on the eye, such as the T-shirt example below.

However, if you want to know the total number of sizes and colors, you don’t want to count those spaces.

In that case, you would use =COUNTA(B2:B18), which would give you a number of 15

Excel: COUNTA example

COUNTBLANK

COUNTBLANK does exactly what you would expect it to do: it counts the number of blank cells.

=COUNTBLANK(A1:C18) gives me 6 because I have 6 blank cells.

Excel: COUNT, COUNTIF, COUNTIFS

Many times in Excel you will want to get an idea of how many records you might have, or you want to count certain things within your records. To do that you can use COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK.

I will cover COUNTA and COUNTBLANK in another post.

COUNT

The COUNT formula counts numbers and ignores empty cells, text fields, TRUE/FALSE logical values. It only counts numerical values.

=COUNT(1,3,”VT”,5,8,”Hokie”) would return 4, as there were 4 numbers within those values.

=COUNT(B1:B18) in the T-shirt example below would return 0 as there are no numbers in the B column.

=COUNT(C1:C18) in the T-shirt example would return 15 as there are 15 numbers in the column.

Excel: COUNTA example

COUNTIF and COUNTIFS

The COUNTIF formula gives you a number count for any criteria you set, text or numerical.

For example, Housing and Residence Life might collect data showing incident reports for their residence halls.

Excel: COUNTIF formula example

Using COUNTIF, we can take all the incident reports from all the buildings, and then pull out information by building, by day of the week, or by type of incident.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria.

=COUNTIF(range, criteria)

The formula looking at the number of incidents in Lee would read as

=COUNTIF($B:$B,E3)

You could also type it as =COUNTIF($B:$B,”Lee”)

COUNTIFS

The COUNTIFS formula lets you set multiple criteria on any cell content, numerical or text.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria, then the next range, then the next criteria and so on.

=COUNTIF(range, criteria, range, criteria)

So using the above example, a formula that reads

=COUNTIFS(B:B,”Lee”,C:C,”Alcohol”)

would return 2 because in the full list of data, two alcohol incidents occurred in the Lee building.

Excel: what does the dollar sign do?

Oftentimes when working with Excel, you’ll notice that dollar signs ($) appear in front of, or in the middle of cell references.

For example:
$A1
A$1
$A$1

What does Excel do with this information and why do you need it?

It means that the row or column which comes after the dollar sign is anchored or absolute, and therefore will stay the same as you copy and paste formulae to another cell.

Excel by default uses “relative” reference to a cell, which means that the formula changes as you copy it to another cell.

For example, a formula in column D that states =SUM(A1:A5) will become =SUM(B1:B5) when it is copied into a cell in column E.

By using the dollar sign, you can ensure that either the row stays the same, the column stays the same, or that you want a specific cell referenced.

So using the references from the above example:

$A1 means that the formula will always pull information from column A, regardless of which column it appears, but it can change the row depending on where the formula appears.

A$1 means that the formula will always pull from the first row, regardless of which row it appears, but it can change the column depending on where the formula appears.

$A$1 means that no matter where the formula is placed on the sheet, it will always reference the A1 cell.

Why do we care?

We generally care if we’re trying to reference information in a list. We don’t want the number of items changing in the list, so if you have information in rows 1 through 20, then you don’t want the first row to have a formula looking at rows 1:20, and the second row to have a formula looking at rows 2:21, etc. You would want to make sure your range at least has dollar signs in front of the row numbers, so that A$1:A$20 will always pull rows 1:20 from the column.

Excel formula: SUM vs SUMIF

Excel is a useful tool any time you want to analyze or manipulate data. I use it regularly and thought I would create a few posts about its usefulness.

Excel uses formulae to perform its calculations. You begin a formula with the equal sign (=) and then you can tell Excel to do a bunch of things, one of which is add numbers in a row or column.

In order to do that, you use the SUM formula.

The SUM formula is generally used on a range of cells.

For example the formula =SUM(A1:A5) will add the first five numbers column A.

If you had the numbers 10, 20, -15, 25, and 30 in the first column, using =SUM(A1:A5) would generate 60 as your total.

Now let’s imagine that you only want the positive numbers to be added. You can use the SUMIF formula to accomplish that. Taking the same numbers above: 10, 20, -15, 25, and 30, using =SUMIF(A1:A5,>0) would generate 85 as your total.

There is a third piece to the SUMIF function that lets you compare non-numerical information, and then add the numbers that match. For example if you had a list of T-shirts in different colors and sizes, and you wanted to grab your totals by size or by color, you would use SUMIF.

Excel: SUMIF function

To give an example, the formula in F4 would read:

=SUMIF(A1:A16,E4,C1:C16)

So let’s take the parts of that:

  • The first part is – look at the information in column A, which is the color column
  • The second part is – compare that information against E4, which is Maroon
  • The third part is – take the numbers from the quantity column to add up the total number of Maroon T-shirts

Using Excel to generate a random number

o365-excelComputers are great at randomization, and if I need to randomly select something, I use Excel to help. Excel has a very handy formula called RANDBETWEEN.

The way to use it: type

=RANDBETWEEN(bottom, top)

bottom
The smallest integer value that the function will return.

top
The largest integer value that the function will return.

NOTE: If bottom is a greater than top, the RANDBETWEEN function will return #NUM! error.

You can compare 2 cells using

=RANDBETWEEN(cell1, cell2)
e.g. =RANDBETWEEN(B2, B3)
Result: a random number between the number in B2 and the number in B3

Or by using actual numbers, like
=RANDBETWEEN(1,22)
Result: a random number (between 1 and 22)

=RANDBETWEEN(100,200)
Result: random number   (between 100 and 200)

=RANDBETWEEN(200,100)
Result: #NUM!           (because bottom is greater than top)

And yes, it will return the lowest number as well as the largest number in the range.

ExcelTip: Text to Columns and Concatenate – two best friends

If you often work with lists, especially those containing names, then you have probably dealt with having to format the data in columns.

If you have ever needed to compare lists, then you probably have needed to rearrange the content in columns.

For example, FirstName LastName, versus LastName, FirstName.

How do you handle making the switch?

Excel makes it (somewhat) easy, with the Text to Columns feature, followed by the Concatenate formula.

You can find Text to Columns under the DATA ribbon. There are a number of settings you can choose from, and I tend to use delimited, and space or comma, depending on what I need.

Once you have your data split into columns, check the rows to make sure you don’t have an extra column with information, which often happens when you have either middle initials, or a middle name without a hyphen.

To combine the columns back into one with the format you need, use concatenate.  Assuming that we start in row 2, your FirstName is in column A, and your LastName is in Column B, the following formula will combine the information as LastName, FirstName.

=CONCATENATE(B2,”, “,A2)

Note that there are commas between each section, and quotes surround the text in the middle. It might be a little difficult to see:  the quotes have a comma followed by a space between them.

What the formula is doing is concatenating, or joining, the information in cell B2, then adding a comma followed by a space, and then adding the information in cell A2.

To learn more about these functions, see