Tag Archives: formulas

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”)

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)

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:-

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:-

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!

Excel Functions and Formulas

A to Z of Excel Functions: COUNT

How many numbers are in the grid below?

Bored of counting them up yet?  Don’t fear, the COUNT function is here!  It takes a range or a selection of individual cells or values as its argument and tells you how many numeric values there are in that list or range.  It looks like this:-

=COUNT(value 1, value 2……all the way to value 30!)

As mentioned, it can also take a range as well as individual comma separated values.  So, let’s use it to find out how many numbers are in our grid:-

As you can see, I’ve used the whole range in which the numbers are situated, in this case A1 to E15.  Press enter and the formula will spit out a value of 69 dude!  Easy!

I mentioned earlier that you can use individual comma separated values as well as ranges, like:-

=COUNT(“Not a number”, “Not a number”, 3, 4, 5, “Not a number”)

which returns 3, obviously.  Good, isn’t it?  You could also use cell references separated by commas to produce the same result.  As you can see, the COUNT function only counts numerical values, it won’t count text or anything which can’t be interpreted as a numerical value (for example error values).

There are a couple more functions which can be used to count things in Excel – this is the first and easiest.  Over the next couple of days I’ll take you through some more of them, so keep coming back!