HomeGoogle DocsSpreadsheet

COUNTIF in an Array in Google Sheets

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

You can use COUNTIF in an array in Google Sheets with ARRAYFORMULA support to expand your formula output across multiple rows.

If you want to count each value in a column against each value, this will help. The result will be alongside the values in each row.

If you just want unique values in a column and their total count, then it is better to use the QUERY function or a combination of UNIQUE and COUNTIF.

This tutorial focuses on various COUNTIF array formulas and alternative techniques in Google Sheets.

How to Use COUNTIF in an Array and Get the Count Alongside

This section describes how to use the COUNTIF function in Google Sheets to count specific values within a range and display the resulting counts next to the original data.

Example:

Imagine you have a table in the range A1:B22 where column A contains the year and column B contains the names of the Wimbledon Men’s Singles Champions from 2000 to 2020.

If you want to find how many times Roger Federer won the title during this period, use the following formula:

=COUNTIF(B2:B22, "Roger Federer")

If you want to get the count of titles each player has won alongside their names, you can use the following COUNTIF array formula in cell C2:

=ArrayFormula(COUNTIF(B2:B22, B2:B22))
Example illustrating COUNTIF usage in an array to get counts alongside each item

This formula follows the syntax COUNTIF(range, criterion) where the range and criterion are the same range references.

The COUNTIF function here counts the values in the specified criterion range within the range to count and returns the count accordingly.

Note: When you extend the range, for example from B2:B22 to B2:B, you should modify the formula as follows:

=ArrayFormula(IF(B2:B="", ,COUNTIF(B2:B, B2:B)))

The IF logical test ensures the formula returns an empty string wherever B2:B is empty.

COUNTIF in an Array and Get the Count Alongside the Unique Values

This type of COUNTIF array formula requires the UNIQUE function. It works like this:

You use the UNIQUE function to get the unique values from the COUNTIF range and use that as the criterion range in the COUNTIF.

As per our example, you can try this as follows:

  1. Enter the following UNIQUE formula in cell D2:
    =UNIQUE(B2:B22)
  2. Then enter the following COUNTIF formula in cell E2:
    =ArrayFormula(COUNTIF(B2:B22, D2:D9))

You are also free to use open ranges. When using UNIQUE, you simply replace B2:B22 with B2:B. But in COUNTIF, use the IF logical test as earlier:

=ArrayFormula(IF(D2:D="", ,COUNTIF(B2:B, D2:D)))
COUNTIF array formula to count occurrences alongside unique values in a column

Now, we will see the QUERY alternative to the COUNTIF array formula above.

QUERY Alternative for a Clean Looking Count

We can use the QUERY function as an alternative to COUNTIF array formulas if you want to find unique values and their counts in a range.

In our example, we can use this QUERY formula to return the unique names of players in column B and the number of titles won by counting their names in the range:

=QUERY(B1:B, "SELECT B, COUNT(B) WHERE B<>'' GROUP BY B", 1)

Syntax:

QUERY(data, query, [headers])

Where:

  • B1:B: Specifies the range from which data will be queried (column B).
  • "SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B": This is the query string in SQL-like syntax:
    • SELECT B: This selects the data in column B.
    • COUNT(B): This counts the number of occurrences of each unique value in column B.
    • WHERE B<>'': Filters out any empty cells in column B.
    • GROUP BY B: This groups the results by the unique values in column B, so the count is performed for each unique value.
  • 1: This indicates that the data range has a header row in the first row.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Sheets Vs Excel Formula

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...
Sheets Vs Excel Formula

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...
Excel Formula

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...
Excel Formula

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

44 COMMENTS

  1. Hi, I have 6000 products, with values 1-100, some are current assortment products, and some are previous assortment values.

    I’m trying to count how many current and previous products have each value from 1 to 100.

    I’m trying to use the countifs formula with two conditions – “ex” or “current” and value (ranging 1-100).

    However, it is not working in google sheets:

    =COUNTIFS('db prod'!$D:$D,E$4,'db prod'!$F:$F,$D5)

    Wherein;

    1. Column F on the ‘db prod’ sheet I have values 1-100, in cell D5 is value 100.
    2. Column D on the ‘db prod’ sheet contains “ex,” “current” or “potential,” and E$4 contains the value “ex.”

    May you help me?

  2. Hi,

    I’m looking for a formula that counts a range of rows as one count if one or more cells in the range contain a specific text.

    Please find my sheet for more explanation and the desired result at the bottom of this comment.

    I would be very glad if you could help me.

    — sample sheet link removed by admin —

  3. Hi,
    I am looking to reverse the count with an arrayformula of row occurrences on two columns. Could you help me?

  4. I am looking for a way to use ARRAYFORMULA(COUNTIF(... to count occurrences of a particular value across several columns of each ROW — as opposed to counting reoccurring values all in the same column — facing the same problem, that ARRAYFORMULA(COUNTIF(A1:D99,"value") returns a single number, rather than an array showing the COUNTIF for each row. Any suggestions on how to do this?

  5. In column B, I have Stock names of about 4000 rows.

    For each company, every day, I add a data point. So, a column gets added each day.

    In column C, I want the last 20 days’ values separated with the comma.

    As the last column keeps changing, I use the indirect function. So, the two indirect functions keep track of the shifting columns – the start point and the last point. I hope I make sense.

    • Hi, SPH,

      We can do that using either of the below two formulas.

      Formula 1:

      =join(",",array_constrain(SORT(filter({C2:C,row(C2:C)},B2:B<>""),2,0),20,1))

      Formula 2:

      =textjoin(",",1,query(C2:C,"Select * offset "&counta(C2:C)-20))

      Even though the results are the same, the formulas return the result in different orders.

  6. Thank you.

    Another example of arrayformula challenge:

    =TEXTJOIN(char(44),,indirect($A$1&row()):indirect($B$1&row()))

    How to make this expand vertically?

    Thanks

  8. I am collecting student attendance form responses for multiple groups and multiple subjects.

    I want to make a checkbox if that student is signed in for that day for that subject. I am trying to use countifs but keep getting errors.

    If you can help that would be amazing.

  9. Hi,

    I want a formula for the latest occurrence for the following example

    time stamp|name|choc name
    10/10/2020 10:10:10|nani|dairymilk
    5/4/2020 06:30:00|teja|Cadbury
    10/10/2020 11:10:10|nani|kitkat
    10/10/2020 12:30:10|nani|5 star
    5/4/2020 06:31:00|teja|perk

    This is the data in one sheet.

    And in another sheet I have names and I want the latest preference of chocolate by matching the name (if I matched with “teja” I have to get “perk” if I match with “nani” I have to get “5 star”.

      • Hi, Jahnavi,

        Thanks for giving me the required details.

        Assume the data that you have provided is in A1:C in “Sheet1”.

        The criteria are in A1:A in “Sheet2”

        Then the below Vlookup in cell B1 in “Sheet2” will take care of your requirement.

        =ArrayFormula(IFNA(vlookup(A1:A,sort(Sheet1!B2:C,Sheet1!A2:A,0),2,0)))

        If you face any issue, make a sample sheet as per the above details and share it with me.

  10. I like your Arrayformula Query solution, but I can’t think of how to make it work horizontally.

    So I have a Countif every time A is mentioned in a row like so:

    COUNTIF A B D A F G
    COUNTIF B A D F A G
    COUNTIF G B D A F G
    COUNTIF A A D A F A

    2
    2
    1
    4

    And I would like to slap an arrayformula in front, but it’s only supposed to sum up results when it finds an A in that row, rather than all the rows. And I want any new data o to have that formula in that cell as well.

    Let me know if you know how to do this, much appreciated 🙂 I’ll also keep looking for solutions.

    • Hi Julia,

      The range used in the test: B2:G.

      The formula in cell A2.

      =ArrayFormula(if(len(B2:B),(mmult(if(B2:G="A",1,0),transpose(column(B2:G))^0)),))

      Note: Assume the above data is in B2:G5. My formula expands based on values in column B. So make sure that there is no cell blank in the range B2:B5. If you want to make a cell blank, at least leave a space (tap space bar) (applicable to only in the first column, i.e. column B).

      This article will give you some insight into MMULT in infinite rows.

      Proper Use of MMULT in Infinite Rows in Google Sheets

        • Hi, Dale,

          To try, please share a sheet that contains some sample data within one tab and your expected result within another tab.

          You can leave the URL in the reply. I won’t publish it.

  11. Hi, Prashanth

    I’m looking for a function that gives a horizontal vector with an automatic variable length. let me explain myself.

    I have a column A where days are put together with hours in the following order DD/MM/YYYY hh:mm:ss, but they aren’t sequential or series based on a pattern. This is updated by GPS s installed in trucks.

    So whenever an event happens (no matter the frequency) it will write in column A (DD/MM/YYYY hh:mm:ss), in column B type of event, like, Engine Stop, Change of direction N S W E, Engine Start, etc, etc. Then in column C, I have the exact location of the event.

    As written above, no series involved in the process, and I want a formula to count how many unique days are in column A to put that number inside a formula like the following:

    =ARRAYFORMULA(SEQUENCE(1,"I need the formula to count unique days",A2,1))

    • Hi, Gonza,

      I could understand you want to dynamically use the [columns] in SEQUENCE.

      Syntax:

      SEQUENCE(rows, [columns], [start], [step])

      To get the number of unique days ( to use as [columns]) from a timestamp column (here column A), you can use this formula.

      =ArrayFormula(count(unique(int(A2:A))))

      Then your above-requested formula will be as follows.

      =sequence(1,ArrayFormula(count(unique(int(A2:A67)))),A2,1)

      Note: Format the populated date (date values) to Date using Format > Number > Date.

      But I think you may want something like this (Max Date – Min Date) as [columns]

      Date Difference Based on Max - Min Date in a Column

      Try both.

      Best,

  12. Hi Prashanth,

    Is it possible that I want to count occurrences of items using arrayformula with Countif function?

    Example:

    Item | Occurrences
    apple | 3
    apple | 2
    apple | 1
    orange | 2
    orange | 1
    cherry | 1

    I am using this formula countif(A2:A,A2)

    So, I was thinking if we can use ArrayFormula to count occurrences without specifying values such as “apple”
    Thank you, 🙂

  14. OK, I’ve moved forward on my own, but now I have a new question. I am pulling counts with criteria, but I want to replace the day counts (7 and 10 below) with results from a Vlookup. Can it be done?

    =IF(ISBLANK(A2),"",COUNTIFS(Latest!$E$2:$E,A2,Latest!$F$2:$F,""&today()-10))

  15. Wow, I have a lot to learn. Maybe you can help. I have a sheet with tabs that represent the stages a customer moves through in our funnel, from NEW to PAID to COMPLETED the training (there are actually 7 stages, but 3 will do for example)

    Their email address is the key, and it appears on each of the TABS if they have completed that stage, along with the date of completion.

    For each tab, I want to basically do this type of count:

    COUNT IF the email is not in any of the sheets to the right (later stages not yet reached)

    Also, for an aging report, I want to add “AND DATE IS > X DAYS AGO”

    Not sure how to use/combine query w/ Vlookup. Thanks for any guidance!

  16. Hello Prashanth,

    I need your help with the the below query.

    For your above example. Let us consider three more additional columns, “Prize Money Won” that is unique value for each year and then a column, that is “Donated to Non-Profit” that is donated from the player to non – profit and then the third one would be the balance prize money that is residual of (Prize money won – Donated Amount).

    The result should include the balance = total prize money – total amount donated (all titles included – group by each player).

    thank you

  17. Hi there!

    I have a calculation that contains a Countifs formula, and because it contains a check to see whether a date takes place in a month and year, an Arrayformula needed to be added.

    I’m hoping to find a way where I don’t have to copy + paste the formula for new values.

    This article almost seems to offer the solution, but I can’t really find it out… Do you know what I need to do?

    This is how it looks now:

    =ARRAYFORMULA(COUNTIFS(Blad1!$A:$A;$B5;year(Blad1!$E:$E);F$1;MONTH(Blad1!$E:$E);F$2))

    B5 = User ID
    F1 = a cell with value ‘2019’
    F2 = a cell with value ‘1’ for January

    • Hi, Boudewijn,

      You didn’t mention the cell containing your formula. Also when you copy paste the formula what changes you are expecting in the cell references in the formula.

      Best,

  18. I want to apply a Countif formula for each row, to count the occurrences of a value every time. For this, I dragged the formula to the last row and it worked fine. But the problem arises when a new row is added automatically in the Google Sheet (due to Google Form response). The Countif formula is not added to the new row.

    For this, I applied ARRAYFORMULA with Countif to automatically update the formula with the addition of the new row. It is working but the ARRAYFORMULA is giving the highest occurrence number for every match.

    Please help me to solve this.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.