Category Archives: Functions and Formulas

Excel Functions and Formulas

Making some old code better – Clean and Trim

It pleases me to look back at stuff I did years back and to see that I’ve improved and learned more as time has passed.  Almost three years ago, on teachr I posted this article about a new UDF I’d created and how it could be a little clunky if a large range was selected.

Well, today I’ve fixed that problem.  Here’s some updated code:-

Sub CleanandTrim()
Application.ScreenUpdating = False
On Error Resume Next
Dim cell As Range
For Each cell In Selection
If cell <> “” Then
cell.Value = Application.WorksheetFunction.Clean(Application.WorksheetFunction.Trim(cell))
End If
Next
Application.ScreenUpdating = True
End Sub

The key difference in this code is that I’ve added an IF statement to check to see if anything exists in cells being checked.  If the cells are blank then the code will move to the next cell rather than cleaning and trimming every cell in the selection.

PS.  Apologies – I usually manage to get the code formatted correctly so it looks like VBA code should, but WordPress is acting like MS-Word today and not listening to any of my instructions about formatting.  Will get it fixed soon, hopefully.

Excel Functions and Formulas

A to Z of Excel Functions: COUNTIF

Today we’re going to get all clever and introduce one of the cool functions in Excel, COUNTIF.  COUNTIF works in much the same way as some of the other counting functions in Excel, except it has a little decision-making functionality built into it to make it all the more powerful.

Take a look at the table below.  How many times does Michael’s name appear?

How many did you get?  The answer is 6, but I bet it was a real pain in the backside counting manually through that list.  Imagine counting through a list a few screens in size.  Ouch.

COUNTIF lets us do this kind of thing easily and through a function.  It looks like this:-

=COUNTIF(Range, Criteria)

Where range contains the cells you want to count from, and the criteria entered are what you want to count within that range.  For our example above, the function would look like this:-

=COUNTIF(A1:D10, “Mike”)
=6

You might have noticed that the ‘criteria argument of the function in this case has been entered within double-quotes, as we are looking for text.

So far, so simple, yes?  Let’s try the same thing, except this time we want to count how many times the number 25 appears in the table below:-


It’s a little bit more of a pain doing it by eye, isn’t it?  COUNTIF makes life simple:-

=COUNTIF(A1:D10, 25)
=5

Simples!  Notice this time that the number 25 is typed without double-quotes as it is numeric in value.  Great stuff, isn’t it?  It’s worth noting here that, if you type 25 in double quotes, like this..

=COUNTIF(A1:D10, “25”)

…the function still returns 5.  Number values can be entered as actual numeric values, or as text.  It’s worth remembering this, as in the next part you have to combine both.  Using the same list we now want to count how many values are in the grid which are greater than 15.  Try entering this:-

=COUNTIF(A1:D10, >15)

..and you’ll get an error.  This is because if you want to do this kind of thing Excel expects that you enter it as text, like this:-

=COUNTIF(A1:D10, “>15”)

Which will return a value of  33.  You would use the same method if you were searching for values less that 20:-

=COUNTIF(A1:D10, “<20”)

Values greater than or equal to 21:-

=COUNTIF(A1:D10, “>=21”)

And so on.  Making sense?  You can also use cell references as criteria, which adds even more power to your formulas.

And that’s it for now.  The COUNTIF function is a simple yet extremely versatile addition to your toolkit and can come in extremely handy in adding some real power to your spreadsheets.  Come back for more functions in the series!

Excel Functions and Formulas

A to Z of Excel Functions: COUNTBLANK

Yesterday I mentioned that Excel has a function for counting empty cells.  It’s (wait for it..) the COUNTBLANK function!!  It works in much the same way as COUNT and COUNTA except that it only allows a range as its criteria, not individual comma-separated values, cells or ranges.

It looks like this:-

=COUNTBLANK(range to search for blanks)

Using the same example from yesterday, let’s have a look at it in action:-

=COUNTBLANK(A1:E15)
Pressing enter will tell us that there are 4 empty cells in the range – kids stuff, really isn’t it?  Come back tomorrow for another counting function!

Excel Functions and Formulas

A to Z of Excel Functions: COUNTA

In yesterday’s post we looked at the COUNT function, the first of a handful of Excel functions which, well…count things in Excel..

Anyway, today we’re going to look at another of these functions – the COUNTA function.  COUNTA works in the more or less the same way as its counterpart in that it returns a value based on arguments supplied either as individual values, cells, ranges or a mixture of all the above.  The big difference with COUNTA is that it counts everything, not just numerical values.  The only thing it doesn’t count is empty cells (although there is a function that does – keep coming back to find out more!).

Anyway, enough of the rambling – COUNTA looks like this:-

=COUNTA(value 1, value 2….all the way to value 30)

Let’s look at an example.  The cells below contain various types of data, including numerical values and text.  I’m using the COUNTA function to see how many cells in the range contain data:-

=COUNTA(A1:E15)
Pressing enter will give us a value of 71.  Great stuff!

Just so you can see how COUNTA is different from the COUNT function, try using COUNT instead and you’ll see a value of 64.  As mentioned yesterday, COUNT only counts numerical values so skips the text and logical values in the range.

And it’s that easy!  Tomorrow there’ll be another counting function, so please do come back for more functiony goodness!