ARRAYFORMULA COUNTIF in Google Sheets: How to Use It

=ARRAYFORMULA(IF(A2:A="","",COUNTIF(A2:A,A2:A)))

In this formula, COUNTIF counts how often each category in column A occurs. ARRAYFORMULA then returns those counts down the column, while the IF condition leaves rows with blank category cells blank.

This pattern works well for transaction-category frequencies, duplicate flags, account-code checks, and other row-level controls. It repeats the relevant count beside each source row. It doesn’t produce a grouped summary or validate whether the category is correct.

Reproducible transaction-category example

Create a tab named Transactions with these headers and illustrative values:

Leave B2:B empty, then enter this formula in B2:

=ARRAYFORMULA(IF(A2:A="","",COUNTIF(A2:A,A2:A)))

The expected results are:

The two arguments inside COUNTIF serve different purposes:

COUNTIF(range, criterion)

In this example, the first A2:A is the range to search, while the second A2:A supplies a separate criterion for each row. Google Sheets counts Groceries, then Rent, then Groceries again, returning the corresponding array of results.

Google documents this behavior in its references for ARRAYFORMULA and COUNTIF.

The formulas shown here use commas as argument separators. Some spreadsheet locales require semicolons instead.

Verify the result

For a simple check, filter or scan Transactions!A2:A6 for Groceries. It appears twice, so every populated row containing Groceries should show 2 in column B.

Now enter Groceries in A7. Because both formula ranges are open-ended, the expected results for the Groceries rows in A2, A4, and A7 become 3. Rent and Utilities remain at 1, and you don’t need to copy the formula into B7.

The category count beside each populated transaction should now agree with the number of occurrences in column A.

Count a fixed list of criteria

The row-level formula uses the transaction categories themselves as criteria. If you maintain a fixed list on a summary tab, use that list as the second COUNTIF argument instead.

Create a tab named Summary:

Enter this in Summary!B2:

=ARRAYFORMULA(IF(A2:A4="","",COUNTIF(Transactions!$A$2:$A,A2:A4)))

Based on the original five-row fixture, the expected results in B2:B4 are 2, 1, and 1. If Groceries is subsequently entered in Transactions!A7, the first result becomes 3.

Unlike the row-level pattern, this version returns one result per criterion instead of repeating the count beside every transaction.

For a single criterion stored in one cell, ARRAYFORMULA isn’t necessary. If Summary!D2 contains Groceries, this scalar formula is sufficient:

=COUNTIF(Transactions!$A$2:$A,D2)

Turn the count into a duplicate flag

You can use a frequency greater than 1 to flag repeated category labels:

=ARRAYFORMULA(IF(A2:A="","",IF(COUNTIF(A2:A,A2:A)>1,"REPEATED","")))

With the illustrative inputs, both Groceries rows show REPEATED.

However, this result indicates only that the selected value occurs more than once. Repeated transaction categories are normally expected, so the formula doesn’t establish that the underlying transactions are duplicates. Checking for duplicate transactions may require a unique ID or a defined combination of fields such as invoice number, vendor, date, and amount.

Common problems and fixes

The array result cannot expand

Only B2 should contain the formula. If a cell farther down the intended output range contains a value, formula, or stray space, Google Sheets may report that the array result wasn’t expanded.

Clear only the unintended blocking content in B3:B, then enter the formula again. Don’t overwrite legitimate data merely to make space for the array.

Blank rows show a count

Without the IF guard, COUNTIF also evaluates blank criteria:

=ARRAYFORMULA(COUNTIF(A2:A,A2:A))

As a result, the formula can display the total number of blank cells beside blank rows. To suppress those results, use:

=ARRAYFORMULA(IF(A2:A="","",COUNTIF(A2:A,A2:A)))

The guard changes the displayed result. It doesn’t delete, validate, or repair the blank source row.

A whitespace-only cell isn’t treated as blank

A cell containing spaces is text, even if it looks empty. Consequently, the test A2:A="" may not suppress it.

For cells containing only ordinary spaces, use:

=ARRAYFORMULA(IF(LEN(TRIM(A2:A))=0,"",COUNTIF(A2:A,A2:A)))

Google Sheets TRIM doesn’t remove every whitespace character. Imported data may contain non-breaking spaces or nonprinting characters. The following version handles ordinary spaces, non-breaking spaces represented by CHAR(160), and characters removed by CLEAN:

=ARRAYFORMULA(IF(LEN(TRIM(CLEAN(SUBSTITUTE(A2:A,CHAR(160)," "))))=0,"",COUNTIF(A2:A,A2:A)))

Even this isn’t a complete normalization method for every possible Unicode whitespace character. Also, the blank test doesn’t make " Groceries " and "Groceries" identical inside COUNTIF. Clean inconsistent source labels before relying on the counts.

A typo becomes a separate category

If one transaction says Grocery while the others say Groceries, COUNTIF treats the typo as a different value. The misspelled row will count only occurrences of Grocery.

Remember that COUNTIF counts matches; it doesn’t compare entries with an approved category list. When categories must conform to a chart of accounts or reporting taxonomy, use data validation or a separate lookup control.

Wildcard or criteria characters change the match

In COUNTIF criteria, * can match any sequence of characters, while ? can match one character. Text beginning with a comparison operator, such as >5, can also be interpreted as a condition instead of an ordinary label. The main example assumes category names without wildcard, escape, or comparison syntax.

If literal asterisks or question marks are valid parts of your categories, escape them with ~ in the criteria or use a literal exact-match method. An exact-match method is also appropriate when labels may resemble COUNTIF conditions.

Similar labels are counted together despite different letter case

COUNTIF isn’t suitable for case-sensitive matching. For example, don’t depend on it to distinguish Rent from RENT. A case-sensitive control requires an EXACT-based formula or standardized source text.

For most finance category lists, standardizing capitalization is easier to audit than treating capitalization variants as distinct categories.

The ranges don’t stay aligned

In the row-level pattern, both arguments should begin at the same data row:

=ARRAYFORMULA(IF(A2:A="","",COUNTIF(A2:A,A2:A)))

Avoid combining an expanding criteria range with a fixed counted range unless that limit is intentional:

=ARRAYFORMULA(IF(A2:A="","",COUNTIF($A$2:$A$6,A2:A)))

A category entered below row 6 would supply a new criterion but wouldn’t be included in the population being counted. This mismatch can produce misleading results, including zero for a later value that exists only outside the fixed counted range.

The formula returns zero

For ordinary labels without wildcard, escape, or comparison syntax, a populated row in the self-referencing example should count at least itself. That assumption doesn’t necessarily hold for criteria-like text such as >5, because COUNTIF may interpret the value as a condition instead of a literal label.

A zero is also possible when the criterion comes from another cell or tab and doesn’t appear in the counted range.

If a criterion unexpectedly returns zero, check:

• Leading or trailing spaces
• Singular versus plural labels
• Different punctuation
• Wildcards or comparison operators in the category text
• Numbers stored differently from the intended criteria
• A counted range that ends before the relevant row
• A reference to the wrong tab or column

Choose the function based on the required output

When a transaction must meet more than one condition, such as category plus status, use COUNTIFS:

=COUNTIFS(category_range,category_criterion,status_range,status_criterion)

If you need to total values rather than count rows, use SUMIF. Counting five software transactions and summing their expense amounts answer different questions.

A pivot table is usually a better fit when a report needs one row per category. By contrast, the row-level ARRAYFORMULA COUNTIF pattern is more useful when the count must remain aligned with each transaction for review, filtering, or exception logic. For formula-based grouped outputs, see How to Use QUERY in Google Sheets for Finance. For related conditional array logic, see ARRAYFORMULA IF in Google Sheets.

A concise troubleshooting checklist

Before changing the formula, confirm that:

1. The formula is entered once in the first output cell.
2. The cells below the formula are empty.
3. Both row-level ranges start at the same data row.
4. Open-ended and fixed ranges are being used intentionally.
5. Blank or whitespace-only source cells receive the intended treatment.
6. Category labels don’t contain unnoticed typos or spaces.
7. Wildcards or comparison operators aren’t changing the intended match.
8. Case-sensitive matching isn’t required.
9. Your locale uses the argument separators shown in the formula.

Where ModelMonkey fits

ModelMonkey is an AI assistant for spreadsheet work in Google Sheets and Microsoft Excel. It can inspect spreadsheet context and help explain, create, or adjust formulas such as the category-count control above. When the workflow presents a preview, you can review proposed spreadsheet changes before proceeding.

Review any spreadsheet assistance before relying on it. Confirm the source range, expected output, and control total before using a generated formula in reporting.

Use ModelMonkey to build or check this category-count formula in your spreadsheet.