Formulas & functions
How a function works, with worked examples you can paste.
- Excel COUNTIF Contains Text: Formula & ExamplesTo count cells that contain a specific text string anywhere within them, use =COUNTIF(range,"*"&text&"*") or simply =COUNTIF(range,"*text*") when the text is hard-coded.…
- Excel COUNTIF Text Value: How to Count Cells with TextTo count cells that contain any text value in Excel, use =COUNTIF(range,"*?"), and to count cells with a specific text, use =COUNTIF(range,"text")…
- Excel COUNTIF Matches Reference Cell: Formula & TipsYes, you can use COUNTIF to count cells that match a reference cell by pointing the criteria argument at the cell reference…
- Excel COUNTIF Specific Cells: How to Count Only Certain CellsTo count only specific cells in Excel, use the COUNTIF function with a range and a condition, like =COUNTIF(A2:A20,"Yes"), which counts how…
- Excel COUNTIF Starts With: Wildcard FormulaTo count cells in Excel that start with specific text, use the COUNTIF function with a wildcard: =COUNTIF(range,"text*"). For example, =COUNTIF(A2:A10,"ap*") counts…
- Excel COUNTIF Wrong Count? Fixes & CausesIf Excel’s COUNTIF is returning an incorrect count, the cause is almost always one of four things: criteria syntax (text vs. numbers,…
- Excel COUNTIF Remove Duplicates: How to Count Unique ValuesThe short answer: Excel’s COUNTIF function does not remove duplicates, but you can combine it with other functions like SUMPRODUCT, or use…
- Excel COUNTIF Over 1: Formula & ExamplesThe direct answer: use =COUNTIF(range,">1") to count cells in a range whose value is greater than 1. For example, =COUNTIF(A2:A10,">1") returns the…
- Excel COUNTIF Range Contains Text: Formula GuideYes, you can use COUNTIF to count cells that contain text, but the exact formula depends on what you mean by “contains…
- Excel COUNTIF Not Text? Use COUNTIF with WildcardsTo count cells that do not contain text in Excel, use =COUNTIF(range,"*") — this counts numbers, dates, and blank cells but excludes…
- Excel COUNTIF Ignore Duplicates: How ToThe short answer: Excel’s COUNTIF function does not have a built-in option to ignore duplicates, but you can count only unique values…
- Excel COUNTIF No Duplicates: Count Unique ValuesThe direct answer: Excel has no single “COUNTIF no duplicates” function, but you can count unique values (ignoring duplicates) by combining SUMPRODUCT…
- Excel COUNTIF Not Certain Text: How to Count Cells That Don’t MatchTo count cells that do not contain a specific text string in Excel, use the COUNTIF function with the “not equal to”…
- Excel COUNTIF Exclude Duplicates: Formula & StepsTo count unique values in Excel while excluding duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) (for numbers and text) or =COUNTA(UNIQUE(range)) in Excel 365/2021. The…
- Excel COUNTIF for Multiple Values: How to UseTo count cells matching any of several values in Excel, use SUM(COUNTIF(range, {"value1","value2",...})) as an array formula (or with SUMPRODUCT for older…
- Excel COUNTIF Highlighted Cell? Use These MethodsNo, Excel’s COUNTIF function cannot directly count cells based on their fill color or font color, because colors are formatting, not data.…
- Excel COUNTIF Does Not Equal Text: Formula GuideTo count cells in Excel that do not equal a specific text value, use the formula =COUNTIF(range,"text"). For example, =COUNTIF(A2:A10,"Pending") counts every…
- Excel COUNTIF Duplicate Values Only OnceTo count duplicate values only once in Excel, you need to count each distinct value that appears more than once, not each…
- Excel COUNTIF Equals Cell: Formula & ExamplesYes, you can use COUNTIF to count cells that equal a value stored in another cell. The formula is =COUNTIF(range, cell_reference), for…
- Excel COUNTIF Date in Cell? Formula & ExamplesTo count cells containing a specific date in Excel, use =COUNTIF(range, date) where date is a date entered with the DATE function,…
- Excel COUNTIF Does Not Contain Specific Text?Yes, you can use COUNTIF to count cells that do not contain specific text by using the wildcard operator <> (not equal…
- Excel COUNTIF Does Not Contain Text: Formula GuideNo, Excel’s COUNTIF function does not have a built-in “does not contain” operator, but you can count cells that do not contain…
- Excel COUNTIF Greater Than: Formula & ExamplesTo count cells in Excel that are greater than a specific value, use the formula =COUNTIF(range,">"&value). For example, =COUNTIF(A2:A10,">5") counts how many…
- Excel COUNTIF Text Criteria: How to Use ItTo count cells that contain specific text in Excel, use the COUNTIF function with the text as the criteria, for example: =COUNTIF(A2:A20,"apple")…
- Excel COUNTIF Contains Text from Another Cell?Yes, use COUNTIF(range, "*"&cell&"*") to count cells that contain the text from another cell. For example, =COUNTIF(A2:A10, "*"&B1&"*") counts how many cells…
- Excel COUNTIF Contains Date? Formula & ExamplesYes, you can use COUNTIF to count cells that contain a date, but the exact formula depends on whether the cells contain…
- Excel COUNTIF Contains Partial Text: Formula & ExamplesYes, you can use COUNTIF for partial text in Excel by combining it with wildcard characters: asterisk (*) matches any sequence of…
- Excel COUNTIF Cell Contains Text (Partial Match)The direct answer: use =COUNTIF(range,"*text*") to count cells that contain a specific text anywhere within them. For example, =COUNTIF(A2:A10,"*apple*") counts every cell…
- Excel COUNTIF Contains Text: Formula & ExamplesTo count cells that contain a specific text string in Excel, use =COUNTIF(range,"*text*"). For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that…
- Excel COUNTIF for Text: Exact Match FormulaTo count cells in Excel that equal a specific text exactly, use =COUNTIF(range,"text"). For example, =COUNTIF(A2:A10,"Apple") counts every cell in A2:A10 that…