Summing Formulas

Sum across worksheetsSumming - Other
Sum across worksheets with 1 conditionSumming - Other
Sum all the individual digits of a numberSumming - Other
Sum by groupSumming - Other
Sum by monthSumming - Other
Sum by yearSumming - Other
Sum cells in a table when a column contains n charactersSumming - Table
Sum cells in a table when a column does not contain a valueSumming - Table
Sum cells in a table when a column is either x or ySumming - Table
Sum cells in a table when a column is equal to 2 valuesSumming - Table
Sum cells in a table when a column is equal to 3 or more valuesSumming - Table
Sum cells in a table when a column is equal to a valueSumming - Table
Sum cells in a table when a column is equal to a value case sensitiveSumming - Table
Sum cells in a table when a column is not blankSumming - Table
Sum cells in a table when a column is textSumming - Table
Sum cells in a table when a column is text and begins withSumming - Table
Sum cells in a table when a column is text and ends withSumming - Table
Sum cells in a table when a column meets 1 conditionSumming - Table
Sum cells in a table when a column meets multiple conditionsSumming - Table
Sum cells that are not errorsSumming - Other
Sum cells that are not hiddenSumming - Other
Sum cells that are numbersSumming - Other
Sum cells that are numbers after multiplyingSumming - Other
Sum cells that are numbers and evenSumming - Other
Sum cells that are numbers and greater than a valueSumming - Other
Sum cells that are numbers and less than a valueSumming - Other
Sum cells that are numbers and negativeSumming - Other
Sum cells that are numbers and oddSumming - Other
Sum cells that are numbers and positiveSumming - Other
Sum cells that are numbers and uniqueSumming - Other
Sum cells that are numbers in a rangeSumming - Other
Sum cells using array formulaSumming - Other
Sum cells with formattingSumming - Other
Sum cells with OR criteriaSumming - Other
Sum cells with shadingSumming - Other
Sum comma delimited numbersSumming - Other
Sum entire columnSumming - Other
Sum entire rowSumming - Other
Sum every n cells in a columnSumming - Other
Sum every n cells in a rangeSumming - Other
Sum every n cells in a rowSumming - Other
Sum numbersSumming - Other
Sum running totalSumming - Other
Sum the last n numbersSumming - Other
Sum the n largest numbersSumming - Other
Sum the n smallest numbersSumming - Other
Sum visible rows in a filtered columnSumming - Other
Sum visible rows with 1 conditionSumming - Other
Sum visible rows with multiple conditionsSumming - Other
Sum whole numbers onlySumming - Other
Sum across worksheets
Summing - Other
Sum across worksheets with 1 condition
Summing - Other
Sum all the individual digits of a number
Summing - Other
Sum by group
Summing - Other
Sum by month
Summing - Other
Sum by year
Summing - Other
Sum cells in a table when a column contains n characters
Summing - Table
Sum cells in a table when a column does not contain a value
Summing - Table
Sum cells in a table when a column is either x or y
Summing - Table
Sum cells in a table when a column is equal to 2 values
Summing - Table
Sum cells in a table when a column is equal to 3 or more values
Summing - Table
Sum cells in a table when a column is equal to a value
Summing - Table
Sum cells in a table when a column is equal to a value case sensitive
Summing - Table
Sum cells in a table when a column is not blank
Summing - Table
Sum cells in a table when a column is text
Summing - Table
Sum cells in a table when a column is text and begins with
Summing - Table
Sum cells in a table when a column is text and ends with
Summing - Table
Sum cells in a table when a column meets 1 condition
Summing - Table
Sum cells in a table when a column meets multiple conditions
Summing - Table
Sum cells that are not errors
Summing - Other
Sum cells that are not hidden
Summing - Other
Sum cells that are numbers
Summing - Other
Sum cells that are numbers after multiplying
Summing - Other
Sum cells that are numbers and even
Summing - Other
Sum cells that are numbers and greater than a value
Summing - Other
Sum cells that are numbers and less than a value
Summing - Other
Sum cells that are numbers and negative
Summing - Other
Sum cells that are numbers and odd
Summing - Other
Sum cells that are numbers and positive
Summing - Other
Sum cells that are numbers and unique
Summing - Other
Sum cells that are numbers in a range
Summing - Other
Sum cells using array formula
Summing - Other
Sum cells with formatting
Summing - Other
Sum cells with OR criteria
Summing - Other
Sum cells with shading
Summing - Other
Sum comma delimited numbers
Summing - Other
Sum entire column
Summing - Other
Sum entire row
Summing - Other
Sum every n cells in a column
Summing - Other
Sum every n cells in a range
Summing - Other
Sum every n cells in a row
Summing - Other
Sum numbers
Summing - Other
Sum running total
Summing - Other
Sum the last n numbers
Summing - Other
Sum the n largest numbers
Summing - Other
Sum the n smallest numbers
Summing - Other
Sum visible rows in a filtered column
Summing - Other
Sum visible rows with 1 condition
Summing - Other
Sum visible rows with multiple conditions
Summing - Other
Sum whole numbers only
Summing - Other

Sum across worksheets

This formula returns the sum for the cell in column "A" for each worksheet from Sheet1 to the worksheet where the function resides

 A
1EVALUATE("SUM(Sheet1:"&ShtName&"!A"&Row()&")")+NOW()*0
??

Sum all the individual digits of a number

??

 A
1{=SUM(VALUE(MID(ABS(Cell_Address),ROW(INDIRECT("1:"&LEN(ABS(Cell_Address)))),1)))}
??

Sum by month

??

 A
1SUMIFS(values,dates,">="&A1,dates,"<"&EDATE(A1,1))
The column contains first-of-month dates, formatted to display just the month
Another way to solve this problem is to use the SUMPRODUCT function and the TEXT function.

Sum cells in a table when a column is text and begins with

??

 A
1=SUMIF(CellRange,"start*",SumRange)
??

Sum cells in a table when a column meets 1 condition

??

 A
1{=SUM(IF(ISERROR(CellRange),"",IF(CellRange>15,CellRange)))} = 402
2copy from image
refer to Conditional Formatting
To sum values in a range that may contain errors you can use the following "=SUM(SUMIF("A2:A20","<0"),SUMIF("A2:A10",">0"))".
2) Getting subtotals [Using a single cell array formula]
This example illustrates two methods that can be used to obtain subtotals from a table of data.
The formulas in cells C12 & C13 use the plus operator to sum the individual cells.
Entering these manually would be quite time consuming if the list was any longer. Each cell must be added manually to the formula.
The formulas in cells C15 & C16 use an array formula to add up the subtotals and must be entered using (Ctrl + Shift + Enter), otherwise #VALUE! will be returned.
These array formulas use two 1-dimensional arrays, C3:C10 and D3:D10.
The IF function compares the corresponding values in the first array C3:C10 and then returns the corresponding value from the second array D3:D10.
The SUM function is then passed this new array (which exists only in memory) and returns the total value.

Sum cells in a table when a column meets multiple conditions

??

 A
1{=SUM ( (cellrange > 0) * (cellrange < 20) ) }
2SUMIF( criteria_range, "text1", values_range) + SUMIF(criteria_range, "text2", values_range)
Refer to Conditional Summing

Sum cells that are not errors

The SUM function does not return a sum if any of the cells contain errors. You can use "=SUMIF("A2:A10",<>"#VALUE!")".

 A
1{=SUM(IF(ISERROR(CellRange),"",CellRange))} = 623
SUM
IF
ISERROR
SUMIF

Sum cells that are numbers

??

 A
1??
This formula will add up numbers.
SUM
SUMIF

Sum cells that are numbers after multiplying

??

 A
1={SUM(A1:A10*B1:B10)}
You can use SUMIFS

Sum cells that are numbers and greater than a value

??

 A
1{=SUM( IF (cellrange > 0, cellrange))}
SUM
IF

Sum cells using array formula

??

 A
1copy from image
1) Adding up values [Using a multi cell array formula]
This example illustrates two methods that can be used to add two block of numbers.
The formulas in rows 8 and 9 use the SUM worksheet function to add up the values.
The formulas in rows 11 & 12 use an array formula to add up the values and must be entered using (Ctrl + Shift + Enter). This array formula performs the necessary calculation and then returns a four-item array.
Excel inserts curly brackets {} around the formula to indicate that it is an array formula. These brackets cannot be entered manually.
The array formula "{=A1:B2+A4:B5}" returns an array containing four numbers.
The formula must therefore occupy four cells since you cannot display more than one number in a cell.

Sum comma delimited numbers

??

 A
1EVALUATE(SUBSTITUTE(A1,",","+"))
If you had comma-delimited values in cell A1 then the following function will return the sum of these values.
It replaces the commas with plus signs and then evaluates.

Sum every n cells in a row

??

 A
1{=SUM(cellrange * (MOD(ROW(cellrange), 3) = 0))}
This formula will sum every third row

Sum the n largest numbers

??

 A
1SUM( LARGE( B2:D5, {1,2,3} ) )
2{=SUM(LARGE(cellrange, {1,2,3,4}))}
3LARGE(B2:D5, ROW(INDIRECT("1:3")))
4=SUMPRODUCT(LARGE(CellRange,{1,2,N}))
5{=SUM(LARGE(CellRange, ROW(INDIRECT(Row(CellRange) & ":" & Rows(CellRange)))))}
This formula will sum the largest 3 numbers
This formula will sum the largest 4 numbers
When you use this formula the values will not be adjusted when you insert rows or move the array formula
SUM
LARGE
SUMPRODUCT
ROW
INDIRECT

Sum the n smallest numbers

??

 A
1SUM( SMALL( B2:D5, {1,2,3} ) )
2{=SUM(SMALL(CellRange, ROW(INDIRECT(Row(CellRange) & ":" & Rows(CellRange)))))}
This formula will sum the smallest 3 numbers
SUM
SMALL
ROW
INDIRECT
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited Top