Whilst I'm on this Excel kick, let's move on to another useful math function: the COUNTIF function.

With COUNTIF, Excel can search a range of data and count the number of times it finds a specific datapoint or condition. The function's arguments are constructed like so:



In the function, the "range" designates the cell range Excel will be searching. The "criteria" designates the specific datapoint for which Excel is to search and count occurrences.

In the simple table at left, we see a listing of fictional sales transactions and items. Suppose we wanted Excel to count the number of sales of our Zonger product. The COUNTIF function is what we'd use to make it happen!

Since Column C contains the names of the sold items, the range "C5:C15" will constitute the "range" argument in the COUNTIF function. We'll use Cell F5 as our "criteria" argument (see picture below). In it, we've typed the name of the product whose sales we wish to count.

In this case, we'll put the COUNTIF function in Cell F6. It appears as:

=COUNTIF(C5:C15,F5)

And a finished spreadsheet (albeit aesthetically blah) might look like this:



As we see, Excel has correctly determined that we sold three Zongers in this invoice batch.

Users who wish to download the above spreadsheet, with its sample usage of the COUNTIF function, can grab it here.

Labels: Excel, Tutorials