Formulas & functions
How a function works, with worked examples you can paste.
- Excel Sum a Row Based on a Column Header with SUMIFTo sum values across a row when their column headers match a label, use SUMIF: with headers in B1:G1, row values in…
- Excel Sum Positive Values Only: Formulas and ExamplesTo sum only positive values in Excel, use SUMIF with the criterion ">0". If the numbers are in A2:A8, enter =SUMIF(A2:A8,">0"); Excel…
- Excel SUMPRODUCT for Two Columns: Formula and ExamplesTo multiply corresponding values in two Excel columns and add the products, use =SUMPRODUCT(A2:A5,B2:B5). If A2:A5 contains 2, 3, 4, 5 and…
- Excel Sum of Filtered Data: SUBTOTAL, AGGREGATE & FILTERTo sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the filtered range, replacing C2:C7…
- Excel SUM OFFSET: Create Flexible, Expanding TotalsIn Excel, combine SUM and OFFSET to total a range whose size or position can change: for example, =SUM(OFFSET(B2,0,0,D1,1)) sums a number…
- Excel Sum Negative Numbers: Formulas, Filters, and FixesExcel’s SUM function already includes negative numbers when you add a range: their values reduce the total. To add only the negative…
- Excel Sum Multiple Cells: Formulas, Ranges, and TipsTo sum multiple cells in Excel, use the SUM function: enter =SUM(A1:A5) to add the values in cells A1 through A5. For…
- Excel Sum Multiple Columns: Formulas, Examples, and TipsTo sum several columns in Excel, use =SUM(B2:D10) for one continuous block of cells. For separate columns or ranges, list each range…
- Excel Sum Lookup: Sum Values That Match a CriterionTo add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10, amounts in B2:B10, and…
- Excel SUMIF Not Blank: Sum Values When Cells Have DataWith the cells to check in A2:A10 and the numbers to add in B2:B10, use =SUMIF(A2:A10,"<>",B2:B10) to sum values in column B…
- Excel SUM Ignore Errors: Formulas for Clean TotalsYes. To sum numbers while treating error cells as zero in Microsoft Excel for Microsoft 365, use =SUM(IFERROR(B2:B6,0)); alternatively, use =AGGREGATE(9,6,B2:B6) to…
- Excel SUMIF Less Than: Formula, Examples, and TipsIf the values to test are in A2:A6 and the amounts to add are in B2:B6, use =SUMIF(A2:A6,"<100",B2:B6) to add the amounts…
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in B2:B8, products in C2:C8,…
- Excel SUM Function: Does It Count Filtered Values?Yes. In Excel for Microsoft 365, SUM adds every numeric value in its referenced range, including values in rows hidden by a…
- Excel Sum Highlighted Cells: Quick Methods and FormulasTo add numbers in cells you have selected, select them and read Sum on Excel’s status bar; to total cells with a…
- Excel SUMIF: Sum Values When a Checkbox Is CheckedTo add only the values whose checkboxes are checked, put each checkbox’s TRUE/FALSE value in one column and the amounts in another,…
- Excel Sum Every Other Column: Formulas and ExamplesTo sum every other column in an Excel row, use =SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=0)*B2:K2). With numbers in B2:K2, this adds B2, D2, F2, H2, and…
- Excel Sum Every Other Row: Formulas for Both PatternsIf your values are in A2:A20 and you want to add A2, A4, A6, and the rest of that alternating pattern, use…
- Excel SUM Column Formula: Examples and Common FixesTo add numbers in a column in Excel, use the SUM function. If the values are in A2 through A10, enter =SUM(A2:A10)…
- Excel Sum Dynamic Range: Formulas That Grow With DataTo make an Excel sum include new rows automatically, convert the data to an Excel Table and sum its column: with a…
- Excel Sum by Date: Formulas for Daily and Monthly TotalsTo sum values for a date in Excel, use SUMIF with the date column as the criteria range and the amounts column…
- Excel Sum by Month: Formulas, Dates, and PivotTablesTo sum values by month in Excel, use SUMIFS with a start date for the month and an exclusive start date for…
- Excel Sum Based on Criteria with SUMIF and SUMIFSIf categories are in A2:A7 and amounts are in B2:B7, use =SUMIF(A2:A7,"East",B2:B7) to add the amounts for East; if the matching amounts…
- Excel Sum by Category: Formulas and PivotTablesTo sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and the category to total…
- Excel SUM Based on Another Column: Formulas and ExamplesTo sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5 and amounts in B2:B5,…
- Excel Sum Across Sheets: Formulas and 3D ReferencesTo sum the same cell across consecutive worksheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). If the sheets are not…
- Excel SUM Array: Formulas, Examples, and Common FixesTo add the values in an array or range in Excel, use =SUM(A2:A10) when the numbers are in A2:A10. In Microsoft 365…
- Excel SUM and AVERAGE: Formulas, Examples, and TipsIn Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells B2:B6, enter =SUM(B2:B6) to…
- Excel Sum a Range of Cells: Formulas, Tips, and FixesTo add a range of cells in Excel, use the SUM function: if the numbers are in A1 through A10, enter =SUM(A1:A10)…
- Excel MAX of Two Values: Formula and ExamplesTo find the larger of two values in Excel, use =MAX(A1,B1), replacing A1 and B1 with the cells you want to compare.…