Formulas & functions
How a function works, with worked examples you can paste.
- 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 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 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 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…
- Excel Count Unique Values: 6 Ways to Ignore DuplicatesTo count unique values in Excel while ignoring duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) for a single column, or =COUNTA(UNIQUE(range)) in Excel 365/2021. The…
- Excel COUNTIF Contains Partial Text: 3 WaysTo count cells that contain part of text in Excel, use =COUNTIF(range,"*text*") — the asterisks are wildcards that match any characters before…
- Excel Conditional Formatting If No Formula?Yes, Excel conditional formatting can work without a formula; you can use built-in rules like Highlight Cells Rules, Top/Bottom Rules, Data Bars,…
- Excel Conditional Formatting If Not a Formula?Excel conditional formatting does not require a formula; you can apply formatting based on cell values, text, dates, or even blanks using…
- Excel Conditional Formatting Icon Sets: Formula GuideYou cannot directly type a formula into the icon set rule dialog box. Instead, you use a formula to create a new…
- Excel Conditional Formatting Formula If TextThe core formula for conditional formatting based on text in Excel is =ISNUMBER(SEARCH("text", A1)) for partial matches, or =A1="exact text" for exact…
- Excel Conditional Formatting Formula: If Then RulesThe direct answer: in Excel conditional formatting, an “if then” rule is a formula that returns TRUE to apply formatting and FALSE…
- Excel Conditional Formatting Formula if Zero?To highlight cells that equal zero in Excel, use the formula =A1=0 in a conditional formatting rule. Select the range, go to…
- Excel Conditional Formatting Formula if BlankThe formula to highlight blank cells in Excel conditional formatting is =ISBLANK(A1) (or =A1=""), applied to the range you want to check.…
- Excel Conditional Formatting Formula If Date: Full GuideThe core formula for conditional formatting based on a date in Excel is =A2=TODAY() (assuming your date is in cell A2), which…
- Excel Conditional Formatting Due Date FormulaThe core formula for highlighting overdue dates in Excel is =A2<TODAY() applied to a range starting at A2, which flags any date…
- Excel Concatenate with Date: Formatting & FormulasTo concatenate a date in Excel while keeping it readable, use the TEXT function inside your concatenation formula, like =A2&" "&TEXT(B2,"mm/dd/yyyy"). Without…
- Excel Conditional Formatting Countif FormulaThe direct answer: you cannot use COUNTIF as a standalone conditional formatting rule, but you can apply a formula based on COUNTIF…
- Excel Combine Unique Lists: 3 WaysTo combine two or more lists in Excel and keep only unique values, use the UNIQUE function with the VSTACK function: =UNIQUE(VSTACK(range1,…
- Excel Combine XLOOKUP with SUM: 2 WaysYes, you can combine XLOOKUP with SUM in Excel by nesting XLOOKUP inside SUM to sum a returned array, or by using…