Share Nairobi Business Courses Posts

The CountIf Function And How To Use It

spreadsheets

CountIf Function

The excel spreadsheet CountIf function allows you to count items in a list or range if they meet criteria that you set.

This is especially useful if you have long lists of metrics or data and want summaries of the totals which are dynamically updated by the function as your data changes.

So as an example, say you have a spreadsheet of monthly suppliers for a particular product. As each supplier delivers their product they are marked off as “Paid” or “Pending” by your cashier.

At any point in time you want to know how many of your suppliers has been paid and how many are still owed money by your business. This will go to helping you better manage cash flow.

This is an example of where the CountIf function can be used.

Format of the Function

The CountIf function takes the format of =Countif(Range,Test)

In the Range you tell the spreadsheet where to look and start counting for your test.

In the test criteria you define what the function should count. Textual tests are enclosed in quote marks like “this”

Using our example above lets say the cashier creates a spreadsheet with the suppliers name in Column A. In column B she enters the amount owed for supplies delivered. In column C she enters a comment Paid or Pending to indicate what is owed and what has been paid.

This spreadsheet may become difficult to read especially if it is long in length.

So at the bottom or perhaps at the right hand side of the data in column D onwards she enters the CountIf function as follows.

Paid =CountIf(C2:C234,”Paid”)

and in the row below

Pending =CountIf(C2:C234,”Pending”)

Notes

The range tells the function to look for and count the tests “Paid” and “Pending” in the range from cell C2 to cell C234.

The range data must be spelled EXACTLY as the test in the function otherwise it will not count the items as expected.

When a supplier is paid and their status on the spreadsheet changes from pending to paid the CountIf function will update the totals dynamically.

Alternatively, if a supplier previously marked as paid turns out to be a mistake and reverts to pending, again the spreadsheet function will update the summary totals accordingly.

Click here to view a video for more on how to use the CountIf Function

Our course on spreadsheets can be found here

 

Listen To How To Use The Countif Function – Click Spotify To Hear The Full Episode


Share Nairobi Business Courses Posts