Search Not Just Numbers

Loading...

Tuesday, 13 August 2013

Excel Tip: COUNT, COUNTA and COUNTBLANK

Before we get into this week's post I'd just like to comment on how popular Mynda Treacy's Excel Dashboards course proved with readers. Unfortunately, registration for the course is now finished. Given its success, I will certainly be making it available to readers next time Mynda runs the course - just make sure that you are subscribed to the blog so that I can keep you posted.

In the meantime, you might want to take a look at Mynda's Excel Expert course which is of the same high standard.

OK, on with today's post...

I have covered COUNTIF in a previous post, but today I want to look at three much simpler functions that do a similar task in specific circumstances.

All of the functions have one argument, the range they are to apply to, i.e.

=FUNCTION(range)

NB: there can be multiple arguments, e.g.

=FUNCTION(range1,range2,range3)

or even a list of numbers, although this has less practical use.

So, let's look at each one in turn:

COUNT
This function returns the number of cells that contain numbers within the range or ranges. This does include zeroes. and the results of formulae.

COUNTA
This function returns the number of cells that are not empty within the range or ranges. This does mean anything, including a formula that returns null.

COUNTBLANK
This is essentially the reverse of COUNTA, i.e. it returns the number of empty cells. Again, a formula will not be a blank, irrespective of its result.

Example:


If we apply each of the functions to the range A1:B5 above, we get the following:

=COUNT(A1:B5), returns 4, as the range contains 4 numbers

=COUNTA(A1:B5), returns 8, as there are 8 non-empty cells

=COUNTBLANK(A1:B5), returns 2, as there are 2 blank cells

Not as flexible as COUNTIF but, I'm sure you will agree, much simpler for these specific scenarios.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

4 comments:

  1. Clear and concise. Thank you.

    ReplyDelete
  2. Mohon ijin berpromosi. service Ac segala jenis merk. kami service Ac mencakup wilayah coverage seluruh Surabaya, Gresik, Sidoarjo. kami juga menerima jasa service perbaikan AC bongkar dan pasang AC. pekerjaan kami cepat, tepat, profesional, harga kompetitif di banding harga jasa luaran.
    service ac split sidoarjo
    tukang service ac sidoarjo
    service ac surabaya sidoarjo
    service ac surabaya selatan
    service ac surabaya timur
    service ac surabaya barat
    service ac surabaya utara
    service ac kota surabaya

    SPLIT-TYPE AIR CONDITIONERS
    MULTI-SPLIT AIR CONDITIONERS
    CHILLED WATER FAN COIL UNITS
    VRF AIR CONDITIONERS
    CHILLERS
    WINDOW-TYPE AIR CONDITIONERS

    JASA LAS
    JASA LAS BEKASI

    http://jualjasaacsurabaya.blogspot.com
    Segera hubungi kami, dan kami akan datang kerumah Anda 24 Jam Non Stop.
    (Hari Sabtu / Minggu / Hari Libur buka)
    PIN BBM : 54BE0B4B / 5474CD93 / TELP : 085645475574

    Martabak Paling Enak di Jakarta
    Tempat Tidur Minimalis
    Tempat Tidur Minimalis

    ReplyDelete