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.

Advertisements

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

Pinterest as a productivity tool

In the past I did a post on Pinterest, and how I use it as my bookmarks system for capturing work. I have a couple of work related boards including Office365 resources, SharePoint Pages/Posts for specific references, and SharePointBlogs/Websites for general sites that I have found, that I keep an eye on and reference periodically.

My Pinterest boards come in handy sometimes when I need content for this blog, or if I need to solve a particular problem and I know I have seen an article about that problem. It’s a great filing system. When it comes to productivity tools, OneNote is my favorite productivity tool in Office365, and Pinterest is my favorite productivity in the realm of Social Media.

I am generally not a big fan of the “big brother” aspect of websites where they picks things I might be interested in, and in particular, advertisements based on my viewing history really bug me. Especially around Christmas time. So much for me trying to search for a gift for someone in my household. Next time I load my Facebook or another website, my cookies generate all kinds of ads for those gift ideas. Great way to keep my gift idea a secret, right?

In the case of Pinterest, however, I really don’t mind that it “helps” me. I cannot scour the web for everything related to a topic and so I am glad when Pinterest helps me out by presenting something that might be helpful to me. I have found some very special nuggets that way, and it continues to feed me new ideas.

So if you haven’t used Pinterest before or you haven’t figured out how a good way to use it, try it as a productivity tool. I think you might surprise yourself when you see the wealth of information that’s out there to help you do your job.

Delaying the delivery of all messages

Have you ever sent an email and then hit your forehead with an “Oh Gosh” or more severe expletive when you realized the email you sent was inaccurate, or that you attached the wrong file, or no file at all? Don’t you wish you could retract the email and then send a new one?

The email retraction feature in Outlook is a flawed system and something not to be relied upon. In fact, I don’t even know if it really works since I tend to receive both the original, and the retraction request emails.

I have had a few of these “Oh Gosh” moments, and would have appreciated this tip from Microsoft. By delaying a message it gives you an opportunity to review your email while it’s sitting in the outbox waiting to be sent. Therefore, if you forgot something, you can go back and edit it.

Delaying delivery of all messages.

  • Click the File tab.
  • Click Manage Rules and Alerts.
  • Click New Rule.
  • In the Step 1: Select a template box,
    • under Start from a Blank Rule,
    • click Apply rule on messages I send,
    • and then click Next.
  • In the Step 1: Select condition(s) list, select the check boxes for any options that you want, and then click Next.
    If you do not select any check boxes, a confirmation dialog box appears. If you click Yes, the rule that you are creating is applied to all messages that you send.
  • In the Step 1: Select action(s) list, select the defer delivery by a number of minutes check box.
  • In the Step 2: Edit the rule description (click an underlined value) box, click the underlined phrase a number of and enter the number of minutes for which you want the messages to be held before sending.
    Delivery can be delayed up to 120 minutes.
  • Click OK, and then click Next.
    Select the check boxes for any exceptions that you want.
  • Click Next.
  • In the Step 1: Specify a name for this rule box, type a name for the rule.
  • Select the Turn on this rule check box.
  • Click Finish.

While this tip will help with the “Oh Gosh” moments as described above by giving you time to go back and edit the email before it is sent, it can also help you with setting expectations with your co-workers. If you’re generally someone who answers emails almost immediately, you set an expectation that you will always be available with a quick response. By setting messages to be delayed, you can answer the email right away but the recipient won’t receive it until some time later, thus you won’t give the impression that you’re always at the ready.

Delaying the delivery of an email

Your co-worker is out of the office, and you need to send them something in an email. Do you wait for them to come back and hope you remember to send the email? Do you keep it in your drafts folder and then hit send when they come back? Do you send it now and have it appear in the long list of emails for when they return?

Or… do you write the email now, and then use the delay delivery feature so they won’t receive it until they come back?

The delay delivery feature is a great tool to make sure the email leaves your draft folder, but also make sure your co-worker doesn’t get inundated with the non-urgent type of messaging while they are out on vacation.

Here’s how to do it.

Delay the delivery of a single message

  • In the message, on the Options tab, in the More Options group, click Delay Delivery.
  • Click Message Options.
  • Under Delivery options, select the Do not deliver before check box, and then click the delivery date and time that you want.