Formulas & functions
How a function works, with worked examples you can paste.
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a start-date criterion and an…
- AVERAGEIF Excel Formula: Examples, Criteria, and TipsIn Excel, use AVERAGEIF to average numbers that meet one condition. For example, with departments in A2:A7 and sales amounts in B2:B7,…
- AVERAGEIF Across Sheets in Excel: Formulas & ExamplesExcel’s AVERAGEIF function cannot directly average matching values across multiple worksheets in one formula. Assuming Jan and Feb each have names in…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For an inclusive range of…
- COUNTIFS in Excel: How to Count OR CriteriaCOUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply. To count rows that…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple") for a normal exact…
- Excel COUNTIFS Not Equal to Text: Formula ExamplesTo count cells that are not equal to specific text, use COUNTIFS with the not-equal operator inside quotation marks: =COUNTIFS(A2:A100,"<>Closed"). Add a…
- COUNTIF Excel Range: What to Select and How It WorksIn Excel, the range in COUNTIF is the group of cells the function checks; the basic form is =COUNTIF(A2:A20,"Open"). Select the cells…
- COUNTIFS Excel Array: Criteria, Formulas, and ExamplesIn Excel for Microsoft 365, COUNTIFS can evaluate an array of criteria and return a separate count for each item, usually spilling…
- COUNTIF Excel Partial Text: Match Words with WildcardsYes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the text to search for…
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells. To add values that…
- COUNTIF in Excel: Count Cells That Match a Specific ValueTo count cells in Excel that match a value, use =COUNTIF(A2:A20,"Apple") when the values are in A2:A20 and you want to count…
- COUNTIF Excel String: Exact Text and WildcardsWith the text to search in A2:A8 and the target string Apple, use =COUNTIF(A2:A8,"Apple"); in the example list below, it returns 2.…
- COUNTIF Excel for Specific Text: Examples and TipsTo count cells that match specific text in Excel, use =COUNTIF(A2:A10,"Apple"); to count cells that contain the text anywhere, use =COUNTIF(A2:A10,"*Apple*"). COUNTIF…
- COUNTIF in Excel: Syntax, Examples, and Common FixesIn Excel, use COUNTIF to count cells in one range that meet one condition. If the values you want to check are…
- COUNTIF in Excel: Count Values Less Than a Set NumberTo count values below a number in Excel, use =COUNTIF(A2:A20,"<10"). Replace A2:A20 with your range and 10 with the limit you want;…
- COUNTIF Excel Not Equal: Formulas, Blanks, and ExamplesTo count cells that are not equal to a value in Excel, use =COUNTIF(A2:A20,"<>Apple"). The <> operator means “not equal to”; if…
- COUNTIF in Excel: Count Cells That Contain TextTo count cells in Excel that contain a particular text anywhere in the cell, use COUNTIF with an asterisk on both sides…
- Excel COUNTIF Criteria: Rules, Examples, and FixesIn Excel, COUNTIF criteria are the conditions that tell the COUNTIF function which cells to count. The syntax is =COUNTIF(range,criteria): give it…
- COUNTIF in Excel: Count Between Two NumbersTo count numbers between two limits in Excel, use COUNTIFS with the same range twice: =COUNTIFS(A2:A100,">=10",A2:A100,"<=20"). This counts values from 10 through…
- COUNTIF in Excel Between Two Values: The Right FormulaTo count numbers between two limits in Excel, use COUNTIFS, which applies both limits to the same range. For values in A2:A100…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells that look blank because…
- SUMIFS Excel Expression: Syntax and ExamplesThe SUMIFS Excel expression adds numbers that meet multiple conditions. Its basic form is =SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee"): this adds values in C2:C100 only when…
- SUMIFS Excel Less Than: Criteria and ExamplesTo sum values in Excel only when a related value is less than a limit, use SUMIFS with a criterion such as…
- SUMIFS in Excel: Multiple Criteria, Examples and TipsYes. In Excel, SUMIFS adds numbers only when multiple conditions are met; each criteria pair can test a different range, and all…
- SUMIFS in Excel: Count or Sum Matching Values?SUMIFS in Excel adds numeric values that meet one or more conditions; it does not normally count matching rows. To count rows…
- SUMIFS Excel Criteria Range: How to Set It UpIn Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before adding a corresponding value.…
- SUMIFS Excel: Sum Values That Meet Multiple CriteriaSUMIFS is Excel’s conditional-sum function: it adds values only when all the conditions you specify are met. With sales in E2:E6, regions…
- SUMIFS Excel Column Criteria: Examples and Rules ExplainedTo sum values in an Excel column only when another column meets a condition, use SUMIFS: =SUMIFS(D2:D8,A2:A8,"North"). Here, D2:D8 contains the numbers…
- Excel Formula for Years and Months of Service ExplainedIf the hire date is in A2 and the end date is in B2, use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months" to show completed…