Formulas & functions
How a function works, with worked examples you can paste.
- Excel Date Validation Formula: How to Restrict EntriesThe quickest way to add date validation in Excel is to use Data Validation with a formula. For example, to allow only…
- Excel Dynamic Range Offset: How to Use OFFSETThe fastest way to create a dynamic range in Excel is with the OFFSET function, which returns a reference to a range…
- Excel Date Minus Date in Years? DATEDIF ExplainedThe direct answer: use =DATEDIF(start_date, end_date, "Y") to get whole years between two dates in Excel. For example, =DATEDIF("1/15/2020", "3/10/2024", "Y") returns…
- Excel COUNTIFS Multiple Criteria Range ExplainedThe direct answer: use =COUNTIFS(range1, criteria1, range2, criteria2, ...) where each range must be the same size and shape, and each criteria…
- Excel COUNTIFS Not Blank: Multiple Criteria GuideTo count non-blank cells with multiple criteria in Excel, use the COUNTIFS function with the criteria "" (meaning “not equal to blank”)…
- Excel COUNTIFS Year Equals: How to Count by YearThe direct answer is to use the COUNTIFS function with a date range and a condition that checks if the year equals…
- Excel COUNTIF Wrong Result? Fixes & CausesIf your Excel COUNTIF is returning a wrong result, the most common causes are mismatched data types (text vs. number), extra spaces…
- Excel COUNTIF Year in Date: Formula & ExamplesTo count cells containing a date in a specific year, use =SUMPRODUCT(--(YEAR(range)=year)) or, in Excel 2021 and Microsoft 365, =COUNTIFS(range,">="&DATE(year,1,1),range,"="&DATE(2023,1,1),A2:A10,"="&DATE(B1,1,1),A2:A10,"="&DATE(2023,1,1),A2:A10,"
- Excel COUNTIFS Multiple Criteria Match?Yes, Excel’s COUNTIFS function can match multiple criteria across different columns (or the same column) using AND logic—all conditions must be true…
- Excel COUNTIF Value in Cell? Formula & ExamplesThe direct answer: to count cells that equal a value stored in another cell, use =COUNTIF(range, cell_reference), for example =COUNTIF(A2:A20, B1). This…
- Excel COUNTIF Within a Year: Date Range FormulaTo count cells with dates within a specific year in Excel, use COUNTIFS with two date criteria: =COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"=1/1/2024"). That would count everything…
- Excel COUNTIF Wildcard: Use * and ? in Text MatchesYes, Excel’s COUNTIF supports wildcards: use * to match any sequence of characters, ? to match a single character, and ~ to…
- Excel COUNTIF Then Formula: How to Use ItThe direct answer: there is no single “COUNTIF then” function, but you combine COUNTIF with IF to perform an action after counting,…
- Excel COUNTIF Not Equal to Text: Formula & FixesTo 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 Unique Text: Formula & WorkaroundsTo count unique text values in Excel, there is no single COUNTIF function that does it directly—you need to combine it with…
- 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…