To use COUNTIF in Google Sheets, select an empty cell outside the range you want to count, enter =COUNTIF(A2:A100,"Paid"), and press Enter. Replace A2:A100 with your cells and "Paid" with the value you want to count. The result is the number of cells that match that condition.
The short version
- Basic formula: COUNTIF takes a range and one condition to match.
- Text matches: Put literal text in double quotation marks, and remember that matching ignores capitalization.
- Number comparisons: Put an operator and number together in quotation marks, such as
">20". - Multiple conditions: Use COUNTIFS when each counted entry must meet more than one condition.
| Your task | Formula or approach | Key detail |
|---|---|---|
| Enter a formula on the web | Type in a result cell and press Enter | Keep the result outside the counted range |
| Enter a formula on mobile | Type it or choose Function, then COUNTIF | Complete the arguments and tap Done |
| Count exact text | =COUNTIF(A2:A100,"Paid") | Capitalization does not affect matching |
| Count partial text | =COUNTIF(A2:A100,"*Paid*") | Also matches Unpaid |
| Match wildcard characters literally | Use ~*, ~?, or ~~ in the criterion | The tilde escapes the following character |
| Count a specific number | =COUNTIF(A2:A100,20) | No quotation marks needed around the number |
| Compare numbers | =COUNTIF(A2:A100,">20") | Quote the operator and number together |
| Count checkbox values | =COUNTIF(A2:A100,TRUE) | Use FALSE for unchecked standard checkboxes |
| Match empty text | =COUNTIF(A2:A100,"") | Does not exclusively identify physically empty cells |
| Use a cell as the condition | =COUNTIF(A2:A100,C2) | Leave the cell reference unquoted |
| Copy a count down | =COUNTIF($A$2:$A$100,C2) | Dollar signs keep the counted range fixed |
| Require multiple conditions | =COUNTIFS(A2:A100,"Paid",B2:B100,">20") | Every condition must match |
| Count alternative values | Add separate COUNTIF results | Overlapping conditions can count a cell twice |
| Use a threshold from a cell | =COUNTIFS(A2:A100,">"&C2) | Join the operator and reference with & |
| Count before a date | =COUNTIFS(A2:A100,"<"&DATE(2026,10,1)) | Counts dates before October 1, 2026 |
| Count another tab | =COUNTIF('Order history'!A2:A100,"Paid") | Quote sheet names containing spaces or punctuation |
| Count a table column | =COUNTIF(Task_tracker[Priority],"P0") | References adjust as table data changes |
| Count repeated values | =COUNTIF($A$2:$A$100,A2) | A result above one identifies a repeated value |
| Highlight duplicates | Use COUNTIF in conditional formatting | Align the relative reference with the selection's first cell |
| Get formula entry help | Tools > Suggestion controls | Review suggested corrections before accepting |
| Update a stale result | Choose Automatic calculation or click Calculate | On a computer, check whether manual calculation is enabled |
Enter your first COUNTIF formula
Open your spreadsheet and select an empty result cell outside the cells being counted. For example, if your status values are in A2:A100, select C1 and enter this formula:
=COUNTIF(A2:A100,"Paid")Press Enter to get the count. The first argument, A2:A100, identifies the cells to check, while the second argument, "Paid", supplies the condition. Keep the comma between them and the closing parenthesis at the end.
On Android, iPhone, or iPad, open the spreadsheet in the Google Sheets app and tap the result cell. Enter the same formula, then tap Done. You can also tap Function, choose COUNTIF, and complete the arguments before tapping Done.
Count exact text or part of a word
Put literal text inside double quotation marks when you want an exact match. The formula above counts cells matching Paid, but capitalization does not matter, so Paid, paid, and PAID match the same criterion. COUNTIF does not provide a case-sensitive match.
For a contains match, place an asterisk on both sides of the text. Use this formula when Paid can appear anywhere within the cell's text:
=COUNTIF(A2:A100,"*Paid*")That broader condition can also match Unpaid because it contains the same letters. Use "Paid" when you want the whole value to match, or "Paid*" when the text must start with Paid.
An asterisk matches a sequence of characters, while a question mark matches one character. For example, "?at" can match cat or bat. To search for the actual wildcard characters, use ~* for an asterisk, ~? for a question mark, or ~~ for a tilde in the criterion.
Count numbers and checkbox values
For a number equal to your target, enter the number directly as the second argument. This example counts values equal to 20:
=COUNTIF(A2:A100,20)A comparison needs quotation marks around the operator and number. To count values greater than 20, enter:
=COUNTIF(A2:A100,">20")Change the comparison to ">=20" for 20 or more, "<20" for less than 20, or "<=20" for 20 or less. You can also use = for equality.
Standard checkboxes use TRUE for checked values and FALSE for unchecked values. To count checked values, enter the following formula; replace TRUE with FALSE to count unchecked values:
=COUNTIF(A2:A100,TRUE)If your checkboxes use custom values, count the configured value instead. For an empty-string criterion, use =COUNTIF(A2:A100,""), but do not treat that result as a count exclusively of physically empty cells.
Use a cell as the condition and copy the formula
Store the value you want to count in another cell when you expect to change it regularly. If C2 contains Paid, this formula uses that cell's value as the condition:
=COUNTIF(A2:A100,C2)Leave quotation marks off the cell reference, since you want its value rather than the text C2. Changing the value in C2 lets you reuse the formula for another condition.
To build several counts beside a list of conditions, fix the counted range with dollar signs. Enter this formula beside the first condition in C2:
=COUNTIF($A$2:$A$100,C2)The dollar signs keep the counted range fixed when you copy the formula, while the criterion reference can change to the next row. On Windows or ChromeOS, select the formula cell and the cells below it, then press Ctrl + D to fill down. On a Mac, use Command + D.
Count multiple conditions or alternative values
COUNTIF accepts one criterion, so switch to COUNTIFS when each entry must satisfy multiple conditions. For example, this counts corresponding positions where column A matches Paid and column B is greater than 20:
=COUNTIFS(A2:A100,"Paid",B2:B100,">20")Every condition must match for that position to count. Keep the criteria ranges the same dimensions, as shown by the matching row boundaries in this example.
For alternatives, add separate COUNTIF results together. This formula counts Paid entries plus Pending entries:
=COUNTIF(A2:A100,"Paid")+COUNTIF(A2:A100,"Pending")Use that approach carefully when conditions overlap, since a cell can be counted twice. Exact Paid and Pending conditions are distinct, while broader text patterns may overlap.
A comparison against a value stored in a cell uses an operator joined to the reference with &. For a threshold in C2, use this COUNTIFS formula:
=COUNTIFS(A2:A100,">"&C2)For dates, you can join the operator to DATE instead. This example counts dates before October 1, 2026:
=COUNTIFS(A2:A100,"<"&DATE(2026,10,1))Choose a range on another tab or in a table
To count cells on another tab in the same spreadsheet, put the sheet name and an exclamation mark before the range. Surround names containing spaces or punctuation with single quotation marks, as in this example:
=COUNTIF('Order history'!A2:A100,"Paid")A native table is useful when you want the reference to adjust as table data is added or removed. Select your data and click Format > Convert to table, or follow the steps to make a table in Sheets before writing your count.
Use the actual table and column names in the formula. For a table named Task_tracker with a Priority column, the formula could be:
=COUNTIF(Task_tracker[Priority],"P0")Use COUNTIF to find duplicates
COUNTIF can count how many times a particular cell's value occurs in a range. For the value in A2, enter this formula in a separate result cell:
=COUNTIF($A$2:$A$100,A2)A result greater than one means that value occurs more than once in the selected range. You can highlight duplicates in Google Sheets with conditional formatting when you want to mark repeated values directly.
On the web, select A1:A100 and open Format > Conditional formatting. Under Format cells if, choose Custom formula is and enter:
=COUNTIF($A$1:$A$100,A1)>1Choose the formatting and click Done. Keep the formula's relative reference aligned with the first cell of the selected range, as A1 is here. After reviewing the repeated values, follow the steps to remove duplicates in Google Sheets if you want to delete the extra entries.
Fix an unexpected count or stale result
Start by checking the range boundaries and the condition. Make sure the result cell sits outside the counted range, text has double quotation marks, and a cell reference used as the condition has no quotation marks. If the count is too high, check for wildcard patterns that match more text than intended or added counts whose conditions overlap.
For help entering a formula on the web, open Tools > Suggestion controls and select Enable formula suggestions. Begin entering =COUNTIF( to use suggestions. For correction assistance, select Enable formula corrections in the same menu, and review any proposed correction before choosing Accept.
On a computer, if the result does not update because manual calculation is enabled, open File > Settings > Calculation. Under Calculation Mode, choose Automatic and click Save settings. To keep manual calculation, click Calculate at the top of the spreadsheet whenever you want formulas recalculated.
Frequently Asked Questions
Do you need a paid plan to use COUNTIF?
You do not need a paid plan to use COUNTIF, but you need editing access to the spreadsheet. The function is built into Google Sheets and requires no separate download.
Can you give the counted range a name?
You can create a named range on the web by selecting the cells and opening Data > Named ranges. Enter order_status as the name and click Done. You can then use =COUNTIF(order_status,"Paid") in a result cell.
Can COUNTIF count data from a separate spreadsheet?
You can count those cells after importing them with IMPORTRANGE, provided you have access to the source spreadsheet. In cell A1 of an empty tab, enter =IMPORTRANGE("SOURCE_SPREADSHEET_URL","Sheet1!A2:A100"), replacing the placeholder with the source spreadsheet's URL, and click Allow Access if prompted. Then enter =COUNTIF(A1:A99,"Paid") in a cell outside the imported range.
Can you select the range instead of typing its address?
While editing =COUNTIF( on the web, you can select cells in the sheet, then finish the criterion and closing parenthesis. To select with the keyboard, press F2 or Ctrl + E to toggle range-selection mode, then use the Arrow keys to adjust the range.
Can you edit COUNTIF conditional formatting on an iPhone?
Google Sheets on iPhone and iPad lets you view conditional-formatting rules but does not let you edit them. Use the web app or Android app to change those rules.
