Tally in Google Sheets

What Is Tally in Google Sheets?

Google Sheets has an incredibly useful function which allows tallying data. Tally is nothing but counting occurrences, understand related patterns or track frequencies in your data. For instance, you may tally to count inventory and use Google Sheets to log some data when you count these products. Tally in Google Sheets can be achieved using functions such as COUNT, COUNTA, and COUNTIF. Here, COUNT counts the cells with entries in numbers while COUNTA counts all the non-empty cells. COUNTIF is used to count cells based on specific criteria.

In the example below, we count the cells containing non-empty values using COUNTA.

Tally-in-Google-Sheets-Definition

4 Ways to Tally in Google Sheets?

Using the COUNT Function

The COUNT function is helpful and quick when it comes to tallying numbers. Basically, COUNT is used to tally cells with numeric values. Let us look at the syntax of the COUNT Google Sheets function.

=COUNT(value1, [value2,…])

Here,

  • value1 (required) — it represents the value or cell reference or range to be tallied.
  • value2, value3, etc – These are optional arguments that can be used for tallying.

For example, we have a column filled with numbers. Let us count the number of cells have a number. Using COUNT is the best solution.

Using-the-COUNT-Function

Here’s how you can do it: Go to cell A10 and enter the following formula.

=COUNT(A1:A9)

Using-the-COUNT-Function-1

You get a result 6, which shows the number of cells containing numeric values. Simple, isn’t it?

This COUNT formula counts all the numeric entries in the specified range while ignoring text and empty cells.

In case your data contains text entries, here’s where COUNTA plays a crucial role in counting.

Using the COUNTA Function

COUNTA in Google Sheets ignores empty cells, but counts all other cells containing numbers, formulas, dates, text, and errors. Its formula is similar to COUNT.

=COUNTA(value1, [value2,…])

  • value1 (required) — it represents the value or cell reference or range to be tallied.
  • value2, value3, etc – These are optional arguments that can be used for tallying.

For example, let us use COUNTA in the same example used for COUNT. Apply the following formula in cell A11.

=COUNTA(A1:A9)

Using-the-COUNTA-Function

As observed above, the main difference between the COUNT and is that COUNTA can process those values stored as text.

Using the COUNTIF Function

Conditional Tallying with COUNTIF

Now, let’s look at how to do tallying with COUNTIF. It is a more advanced version of tallying where you must tally cells that meet specific criteria. Suppose we have a list as shown below in Google Sheets. The syntax is as follows:

=COUNTIF(range, criterion)

  • range – The range where we wish to count
  • criterion – The condition used for counting

For example, we have the names of all flowers sold at a florist. To count how many times “Orchids” were sold, use the following function.

=COUNTIF(A1:A12, “Orchids”)

Using-the-COUNTIF-Function

This formula counts only those cells in A1:A12 that contain “Orchids.” Thus, we can use COUNTIF to specify certain conditions.  

You can set different criteria, such as logical comparison, text matches, etc., when using COUNTIF.

Using the COUNTIFS Function

COUNTIFS is used to count cells in a range based on multiple conditions. The syntax of the function is as follows:

=COUNTIFS(criteria_range1criterion1[criteria_range2, …][criterion2, …])

Here, the different conditions are listed as criterion1, criterion2, etc.

  • One range and criterion are mandatory, while the others are optional.

Suppose the colors of the flowers dataset above are specified as well. Let us count the number of times yellow roses have been sold.  Here’s how you do it:

=COUNTIFS(C1:C13, “Roses”, D1:D13, “Yellow”).

Enter this formula in E2. This formula looks through the range C1:C13 for “Roses” and also the range D1:D13 for “Yellow.” It counts the entries that meet both the specified criteria.

Using-the-COUNTIFS-Function

COUNTIFS is a very powerful function as it allows you to mix and match data to count cells that fulfil multiple criteria.

Examples

Google Sheets is a wonderful tool for organizing and tallying data. In the examples below, we’ll look at the different ways of tallying and its application in real-time scenarios. Let us look at these tally in Google Sheets examples that are east to understand. Read on!

Example #1 – Class Election Results

Let’s look at an example of tallying class election results in Google Sheets. We have structured the data as shown below.

Let us use a formula in COUNTIF to find the winners.

We have a column with the candidate’s name selected by 50 students. Let’s count the votes received and find the winner.

Tally-in-Google-Sheets-Example-1

Step 1: Let us see the number of votes received by the three candidates in a class of 50.

To find the first candidates’ votes, let us use the COUNTIF function.

Enter the following function in cell C2.

=COUNTIF(A2:A51, “Roger”)

Example-1-Step-1

Step 2: Press Enter. You get the number of votes secured by Roger. Now, for Emma, repeat the same. Use the following formula in cell C3.

=COUNTIF(A2:A51, “Emma”)

Example-1-Step-2

Step 3: Press Enter. Now, to find the votes for Lissy, use the following formula.

=COUNTIF(A2:A51, “Lissy”). Press Enter. You now have the votes received by all three candidates, and we have a clear winner!

Let us use conditional formatting to show the inner in case you have a large number of candidates. Go to the Google menu bar and choose Format > Conditional Formatting.

Example-1-Step-3

Step 4: Here, under “Apply to Range,” choose C2:C4. Next, under “Format cells if,” enter the following custom formula.

=C2=MAX($C$2:$C$4)

Choose an appropriate background color and press Enter.

Example-1-Step-4

The winner is highlighted.

Example-1-Step-4-1

Example #2 – Inventory Management

In this interesting example, we have the stock details of a home baker, as shown below.

Tally-in-Google-Sheets-Example-2

Step 1: At the end of the day, her employee scans the item codes, which are entered in Column D from rows 2-21.

Example-2-Step-1

Step 2: Now, let us find how many of each item she has to manage the inventory. To find the stock of bread, use the following formula in cell F2.

=COUNTIF(D2:D21,”A01”)

Example-2-Step-2

Step 3: Press Enter. Similarly, for Milk, use the following formula in F3.

=COUNTIF(D2:D21,”B01”)

Example-2-Step-3

Step 4: Repeat the same using the above formulas for all the items by just changing the code.

Example-2-Step-4

Step 5: To find how many numbers should be restocked for each item the next day, use the following formula in G2.

=C2-F2

Example-2-Step-5

Step 6: Now, drag the formula all the way to G6.

Example-2-Step-6

You get an idea of how much was sold and how many items need to be restocked. Simple, isn’t it?

Example 3 – Sales Data Analysis

Let us look at how to analyze the sales data in a shop over a week.

Tally-in-Google-Sheets-Example-3

Step 1: Let’s examine the number of sales made on each particular day from Monday to Saturday. For this, we use the COUNTIF function for different days. It helps us count the number of sales made per day.

=COUNTIF(A1:A15, “Monday”)

Example-3-Step-1

In this formula, we count the rows in Column A containing “Monday.”                 

Step 2: Now, let us count the sales of each day, as shown below.

Example-3-Step-2

Step 3: To find the total sales made on each day, use the following formula, SUMIF.

=SUMIF(A1:A15, “Monday”, B1:B15)

Example-3-Step-3

Now, let us calculate the total sales for each day by replacing the days in the above formula.

Example-3-Step-3-1

Thus, we have all the sales details for analysis, and we can apply the appropriate SUMIF and COUNTIF formulas to the dataset.

Important Things To Note

  1. COUNTA includes textual values as well as numeric but ignores empty cells.
  2. To count the unique values in a range, you can use the COUNTUNIQUE function.
  3. Using COUNTIF, you can also count parts of a cell’s contents using wildcard characters: “?”, “*”. For example, =COUNTIF(A1: A10, “?ing”) counts all four-letter words ending in -ing in the supplied range.
  4. You can use mathematical operators like “=,””>,” “<, “”>=,” etc. to count how many of the numbers are greater than/less than/equal to, etc., another number.

Frequently Asked Questions (FAQs)

1. What is the difference between COUNT and COUNTA?

The COUNT function counts only the numeric values and ignores text values.
The COUNTA function counts all non-empty cells, whether they contain text, numbers, dates, or Boolean values. It ignores empty cells. It also counts cells that look empty but contain an empty string.

2. How to use COUNTIF and COUNTIFS with multiple ranges?

One of the advantages of COUNTIFS is that it can be used with multiple ranges, while COUNTIF works with only one range. For instance, to count the number of rows where the value is “Apple” in Column A and the value in Column B is less than 50, the following is the procedure.
=COUNTIFS(A1:A10, “Apple”, B1:B10, “<50”).
The same can be done using COUNTIF with some slight manipulation as follows:
=COUNTIF(A1:A10, “Apple”) + COUNTIF(B1:B10, “<50”).

3. What are the advantages of using the COUNT functions in Google sheets for tallying?

To tally in Google Sheets, we use the COUNT functions—such as COUNT, COUNTA, COUNTIF, and COUNTIFS. These functions are useful when working with large datasets. They help streamline and simplify the tallying process and analyze and summarize data. Using COUNT functions ensures accurate tallying every time.
Using the COUNT functions helps you process vast amounts of data efficiently without manual checking and errors.

Download Template

This article must help understand Tally In Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Tally in Google Sheets. We learn syntax & how to use LARGE In Google Sheets, examples, working template. You can learn more from the following articles.

COUNTBLANK In Google Sheets

VLOOKUP Two Criteria In Google Sheets

ROUNDUP Function in Google Sheets

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *

CHATGPT & AI FOR MICROSOFT EXCEL COURSE - Today Only: 60% + 20% OFF! 🚀

X