Count Attendance and Absence with Google Sheets COUNTIF

In our campus life and work life, we usually record everyone’s attendance. Today we will introduce you the application of Google Sheets COUNTIF function to count the attendance.

Using a week as an example, we use the following example to show you how to use Google Sheets COUNTIF function to count attendance.

Count Attendance and Absence1

As shown above, attendance and absence are counted correctly by Google Sheets COUNTIF function.

FORMULA

In this example, we have used only the COUNTIF function, without nesting other functions. In a simple case like the one above, we can use COUNTIF function to solve our problem directly.

COUNTIF function belongs to Google Sheets Statistical functions. It counts the number of cells that meet the provided criterion in a certain range.

Syntax:

=COUNTIF(range, criteria)

In this example, we can directly apply this function with entering our own range and criteria.

=COUNTIF(B2:F2,"√") – in G2

EXPLANATION

In this example, the “√” in the cell indicates attendance on the corresponding day. Calculating attendance is equivalent to calculating how many ticks there are in B2:F2.

FULL ATTENDANCE

Use G2 as an example, the range for recording attendance is B2:F2. The condition is “√”. So the formula is =COUNTIF(B2:F2,”√”). We cannot enter “√” in this formula, but we can copy a √ into criteria field and include it in brackets.

In the formula bar, we can expand B2:F2, we can get an array of “√”

=COUNTIF({"√","√","√","√","√"},"√")

Count Attendance and Absence1

Google Sheets COUNTIF function will count the number of times “√” appears in the array.

Obviously, the result is 5.

PARTIAL ATTANDENCE

Copy down the formula. G3 for example, B3 and E3 cells in the absence of “√”, so in this formula, there are only three “√” in the range.

Count Attendance and Absence1

After running the formula, the result is 3.

Count Attendance and Absence1

ABSENCE

If the cell is empty, it means it was absent that day. Just replace “√” with “”.

The formula is

=COUNTIF(B2:F2,"")

Count Attendance and Absence1

Note that there are no spaces between the “”. If we enter a space, such as ” “, we will count how many cells in the range B2:F2 contain spaces.

Count Attendance and Absence1

Related Functions

  • Google Sheets COUNTIF function
    The Google Sheets COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Related Posts

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Cash Denomination Calculator

Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, ...

Cap percentage values between 0 and 100

This article will talk about how to cap the percentage values between 0% and 100% in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able to do this by ...

Calculate Cap Percentages to Specific Value

This article will talk about how to limit the cap percentage of a given amount to a given value in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able ...

Sidebar