## How to use the SUM function

The SUM function in Excel allows you to add values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

### Excel Function Syntax

SUM(*number1*, [*number2]*, ...)

#### Arguments

number1 |
Required. A constant, cell reference or an array that contains numerical values you want to add. |

[number2] |
Optional. Up to 254 additional arguments. |

The SUM function lets you add values in cell ranges, arrays, constants. You can have up to 255 different arguments.

#### Table of Contents

- How to add numbers in a column and return a total (SUM function)?
- How to add numbers in an array (SUM function)?
- How to sum specific cells?
- How to sum numbers from multiple cell ranges (SUM function)?
- How to sum a column with text (SUM function)?
- How to sum boolean values?
- How to create a running total (SUM function)?
- How to sum numbers based on a condition/label/item/category?
- SUM with multiple conditions
- How to sum only visible cells?
- How to sum a filtered column?
- How to sum the entire column?
- How to sum a row?
- How to sum across worksheets?
- How to sum by color?
- How to sum absolute values?
- How to use the SUM function in a macro - VBA example
- What is the shortcut key for the SUM function?
- How to sum values greater than smaller than?
- How to sum values below/above 0 (zero)?
- How to limit SUM function?
- How to sum a column and ignore errors?
- How to sum values by date?
- How to sum values by week?
- How to sum values by month?
- How to sum values by year?
- How to sum values between two dates?
- Get excel *.xlsm file

## 1. How to add numbers in a column (SUM function)?

The SUM function lets you add values in a cell range, like this = SUM(B3:B7), instead of adding values in a formula using the plus sign, like this =B3+B4+B5+B6+B7.

The SUM function lets you type one or multiple cell ranges, in this example only cell range B3:B7 is entered as an argument. See the above picture.

Check out the shortcut key to automatically sum a column.

## 2. How to add numbers in an array (SUM function)?

An array is multiple values enclosed with a beginning and ending curly bracket, you can easily convert a cell range to an array. See instructions below.

Select a cell and type =SUM(B3:B9)

Press with left mouse button on in the formula bar and select B3:B9.

Press F9 and the cell range is converted to an array, like this: =SUM({5,3,6,4,2})

Press Enter.

The SUM function adds the values in the array 5+3+6+4+2 = 20. When you convert a cell range to values you hard-code or create constants in your formula, meaning they never change unless you change the values in the formula.

Cell references on the other hand change if you change the values on a worksheet.

I recommend reading this post: Learn the basics of Excel arrays , if you want to learn more about array formulas.

## 3. How to sum specific cells?

The SUM function allows you to add values from the cells you select. The trick is to press and hold the CTRL key while selecting specific cells to sum. Here are the steps in greater detail:

- Doublepress with left mouse button on a cell, the prompt shows up.
- Type =SUM(
- Press and hold CTRL-key.
- Press with left mouse button on with the left mouse button on cells you want to sum.
- Release CTRL-key.
- Add an ending parenthesis )
- Press Enter.

You can also sum cells based on a condition applied to an adjacent column.

## 4. How to sum numbers in multiple cell ranges?

If you want to add values in multiple cell ranges you simply use a comma between arguments. Check your regional settings if a comma doesn't work for you. You are allowed to have up to 255 arguments in one SUM function.

- Doublepress with left mouse button on a cell, the prompt shows up.
- Type =SUM(
- Press and hold CTRL-key.
- Press and hold with the left mouse button.
- Drag with mouse to select the cell range.
- Release left mouse button.
- Go back to step 4 until all cell ranges have been selected.
- Release CTRL-key.
- Add an ending parenthesis )
- Press Enter.

## 5. How to sum a column with text?

The formula in cell B8 adds the values in cell range B3:B7. 5 + AA + 6 + 4 +2 = 17. The SUM function ignores text strings, in this case AA.

The SUM function ignores text values and boolean values but not error values.

Note, the SUM function ignores numbers stored as text. The image below shows the SUM function in cell B8. Only number 4 is included in the total of cells in cell range B3:B7.

Excel shows numbers stored as text differently, see image above. Text values are aligned left in the cell and numbers are aligned right. Cells containing numbers stored as text show a green arrow in the upper left corner of the cell.

## 6. How to sum boolean values?

Cell range B3:B7 contains boolean values, TRUE or FALSE, however, the SUM function can't add boolean values unless they are converted to their numerical equivalents.

There are multiple solutions to this problem, here are a few:

Formula in cell B8:

Formula in cell B8:

Formula in cell B8:

They all convert boolean values to numerical values.

They need to be entered as array formula, because they do calculations to a cell range containing multiple cells.

Instructions on how to enter an array formula.

- Double press with left mouse button on cell B8
- Type =SUM(B3:B7*1)
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.

The formula in the formula bar changes to {=SUM(B3:B7*1)}

These curly brackets tell you that you have created an array formula, don't enter these characters yourself.

The formula returns 2 because TRUE equals 1 and FALSE equals 0. 1+0+1+0+0 = 2.

Note, you can use the SUMPRODUCT function if you don't want to use array formulas.

Regular formula in cell B8:

## 7. How to create a running total?

The image above shows you numbers in column B.

Enter this formula in cell C3:

Make sure you get the dollar signs right, they are important. The cell reference changes as you copy the formula and paste it to cells below.

Select cell C4 and see how the formula changed in the formula bar. The part of the cell reference without dollar signs changed from B3 to B4.

That part is a relative cell reference and the part with dollar signs is an absolute cell reference.

Read more here: Absolute and relative cell references

This makes the SUM function use a cell reference that grows, in other words, it includes more and more cells creating a running total.

Formula in cell C4 adds numbers from both cell B3 and B4. The formula grows even further in cell C5 and it keeps growing in cells below.

Note, you can double press with left mouse button on the dot in the lower right corner of the cell to automatically copy the cell and paste it to cells below as far as there are populated cells in the adjacent column.

## 8. How to sum with a condition [array formula]

The picture above shows you two columns, column B contains text values and column C contains numbers. The formula demonstrated here allows you to sum by another column.

The formula in cell F3 lets you add numbers in column C if their adjacent value is equal to the value in cell F2:

This formula is entered as an array formula unless you are using Excel 365. I recommend the SUMIF function built exactly for this without entering the formula as an array formula.

### Explaining formula in cell F3

#### Step 1 - Logical expression

The equal sign in B3:B10=F2 lets you compare the values in cell range B3:B10 with the value in cell F2. The equal sign is a logical operator, often used in IF functions.

{"A";"B";"B";"A";"B";"A";"A";"A"}="B"

This logical test returns an array of boolean values:

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}

#### Step 2 - Multiply array with cell range

The parentheses (B3:B10=F2) make sure this part of the formula is calculated first before multiplying with the numbers in cell range C3:C10.

(B3:B10=F2)*C3:C10

becomes

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}*C3:C10

becomes

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}*{2; 1; 10; 6; 8; 2; 1; 2}

FALSE is equal to 0 (zero) and TRUE is equal to 1.

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}*{2; 1; 10; 6; 8; 2; 1; 2}

becomes

{0; 1; 1; 0; 1; 0; 0; 0}*{2; 1; 10; 6; 8; 2; 1; 2}

becomes

{0*2;1*1;1*10;0*6;1*8;0*2;0*1;0*2}

and returns

{0; 1; 10; 0; 8; 0; 0; 0}

#### Step 3 - Sum numbers

The SUM function then adds the number in the array:

SUM({0; 1; 10; 0; 8; 0; 0; 0})

and returns 19 in cell F3. 1+10+8 = 19

## 9. Sum with multiple conditions [array formula]

Adding a second condition to the formula is easy. Simply add your condition to the formula enclosed with parentheses.

This formula is entered as an array formula unless you are using Excel 365. I recommend the SUMIFS function built exactly for this without the need for an array formula.

### Explaining formula in cell G4

#### Step 1 - First condition

The equal sign allows you to compare cells to each other.

B3:B10=G2

becomes

{"A";"B";"B";"A";"B";"A";"A";"A"}="B"

and returns

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}

#### Step 2 - Second condition

C3:C10=G3

becomes

{"Small";"Large";"Small";"Large";"Large";"Large";"Large";"Small"}="Large"

and returns

{FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}

#### Step 3 - Multiply arrays

(B3:B10=G2)*(C3:C10=G3)*D3:D10

becomes

{FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE}*{FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}*{2;1;10;6;8;2;1;2}

becomes

{0; 1; 0; 0; 1; 0; 0; 0}*{2; 1; 10; 6; 8; 2; 1; 2}

and returns

{0;1;0;0;8;0;0;0}

#### Step 4 - Sum values in the array

SUM((B3:B10=G2)*(C3:C10=G3)*D3:D10)

becomes

SUM({0;1;0;0;8;0;0;0})

and returns 9.

The SUMPRODUCT function allows you to perform the same calculation without the need for entering the formula as an array formula.

## 10. How to sum only visible cells?

The image above shows numbers in cell range B3:B7, however, cells B4 and B6 are hidden. The SUM function in cell C9 can't ignore hidden values, you need the SUBTOTAL function to sum only visible numbers.

Formula in cell C10:

How to hide / unhide values?

Note, follow these instructions on how to hide and unhide specific rows:

- Press with right mouse button on on row number.
- A popup-menu appears, see image above.
- Press with mouse on Hide or Unhide.

Tip! Press and hold CTRL key while selecting rows to hide/unhide multiple values at the same time.

## 11. How to sum a filtered column?

The image above shows numbers in cell range C3:C7, however, a filter is applied and rows 4 and 6 are hidden. The SUM function in cell C9 can't ignore filtered values, you need the SUBTOTAL function to sum filtered numbers.

Formula in cell C10:

## 12. How to sum the entire column?

The image above shows a formula that adds all values in a column and returns a total.

Formula in cell E2:

The cell reference is B:B meaning that all numerical values in column B are included in the total.

## 13. How to sum a row?

The image above shows a formula that adds all values in a row and returns a total.

Formula in cell C4:

The cell reference is 2:2 meaning that all numerical values in row 2 are included in the total.

## 14. How to sum across worksheets?

The image above shows a formula that adds values located in cell C3 across three different worksheets. For this to work values you want to add must be located in the same cell across all worksheets.

Formula in cell C3:

Here are the steps I did to create the formula above:

- Doublepress with left mouse button on cell C3, the prompt is shown.
- Type =SUM(
- Go to the first worksheet.
- Press with mouse on the cell containing the value you want to add.
- Press and hold SHIFT key.
- Select the last worksheet you want to include in the total.
- Release the SHIFT key.
- Type an ending parenthesis )
- Press Enter.

The image below shows the tabs I selected to create the formula above.

## 15. How to sum by color?

The short answer is that there is really no way to sum by background color if you want to use formulas, however, a VBA macro can do it.

The long answer is that there is the GET.CELL function that has some serious flaws, one is that it is outdated and may be removed from Excel by Microsoft whenever they feel like it. I'd rather recommend coloring cells using Conditional Formatting and then using the same condition to sum the cells.

This is what the image above shows, I chose to highlight rows blue if the corresponding cell in column B is equal to item "B". Here is how I did it:

- Select the cells you want to highlight, in the example above cell range B3:C10.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the Conditional Formatting button.
- Press with left mouse button on "New Rule...". A dialog box appears.

- Press with mouse on "Use a formula to determine which cells to format"
- Press with mouse on field below "Format values where this formula is true:".
- Type =$B3=$F$2
- Press with left mouse button on "Format..." button. A new dialog box appears.

- Press with left mouse button on tab "Fill" on top menu.
- Pick a color.
- Press with left mouse button on OK. The dialog box is closed.
- Press with left mouse button on OK again.

The formula in cell F3 is explained here: How to sum numbers based on a condition/label/item/category?

## 16. How to sum absolute numbers?

The image above shows a formula in cell C8 that converts negative values to positive values and then adds the values.

Formula in cell C8:

### Explaining formula in cell C8

#### Step 1 - Remove sign

The ABS function converts negative numbers to positive numbers, in other words, the ABS function removes the sign.

ABS(*number*)

ABS(C3:C7)

becomes

ABS({5; -3; 6; -4; 2})

and returns

{5; 3; 6; 4; 2}

#### Step 2 - Add values

SUM(ABS(C3:C7))

becomes

SUM({5; 3; 6; 4; 2})

and returns 20. 5+3+6+4+2 = 20

## 17. How to use the SUM function in a macro (VBA)?

The image above demonstrates a macro that shows a message box with a number representing the total of cell range B3:B7.

'Macro name Sub HLP() 'Show sum of B3:B7 in a messagebox MsgBox Application.WorksheetFunction.Sum(Range("B3:B7")) 'Exit macro End Sub

Microsoft docs: | Application.WorksheetFunction | Sum | Range | Msgbox

## 18. What is the shortcut key for the SUM function?

The animated image above shows how to add totals for a cell range, both vertically and horizontally, using a shortcut key.

The SUM formulas in cell range G3:G7 adds values from the cell to the left of the formula and on the same row.

The SUM formulas in cell range C7:F7 return a total based on the numbers above the formulas in the same column.

Here is how to create the SUM function using a shortcut key:

- Select the cell range containing numbers.
- Press and hold Alt key.
- Press the equal sign =
- Release the Alt key.

If the steps above don't work for you try Alt + Shift + 0 (zero) keys. It really depends on your keyboard layout which keys you need to press.

The ímage above shows that you can use the shortcut key below numbers in a column.

## 19. How to sum values below/above a threshold?

The image above demonstrates two array formulas in cell E3 and E5 that return a total with values larger or smaller than a given threshold.

Array formula in cell E3:

Array formula in cell E6:

I recommend the SUMIF function built exactly for this without entering the formula as an array formula.

### Explaining formula in cell E3

#### Step 1 - Filter values above a threshold

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(B3:B7>E2, B3:B7, )

becomes

IF({3;9;2;4;6}>5,{3;9;2;4;6},)

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE},{3; 9; 2; 4; 6},)

and returns

{0;9;0;0;6}

#### Step 2 - Sum values

SUM(IF(B3:B7>E2, B3:B7, ))

becomes

SUM({0;9;0;0;6})

and returns 15 in cell E3.

Back to top

## 20. How to sum values below/above 0 (zero)?

The image above demonstrates two formulas in cells E2 and E3 that return a total with values above and below zero respectively.

Array formula in cell E2:

Array formula in cell E3:

I recommend the SUMIF function built exactly for this without entering the formula as an array formula.

### Explaining formula in cell E2

#### Step 1 - Filter values above a 0 (zero)

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(B3:B7>0, B3:B7, )

becomes

IF({1;-2;3;4;-3}>0,{1;-2;3;4;-3},)

becomes

IF({TRUE;FALSE;TRUE;TRUE;FALSE},{1;-2;3;4;-3},)

and returns

{1;0;3;4;0}

#### Step 2 - Sum numbers

SUM(IF(B3:B7>0, B3:B7, ))

becomes

SUM({1;0;3;4;0})

and returns 8 in cell E2. 1 + 3 + 4 = 8.

Back to top

## 21. How to limit the SUM function?

The image above demonstrates a formula in cell E3 that sums values in cell range B3:B7 and returns a total that is limited to the number specified in cell E2. In other words, the total can't be larger than the value in cell E2 but it can be smaller.

The image above also shows a formula in cell E6 that adds values in cell range B3:B7 and returns a total that is limited to the number specified in cell E5. The total can't be smaller than the value in cell E5 but it can be larger.

Formula in cell E3:

Formula in cell E6:

### Explaining formula in cell E3

#### Step 1 - Sum numbers

SUM(B3:B7)

becomes

SUM({5;6;6;7;2})

and returns 26. 5+6+6+7+2=26

#### Step 2 - Return the smallest number

The MIN function returns the smallest number in a cell range or array.

MIN(E2, SUM(B3:B7))

becomes

MIN(20, 26)

and returns 20. 20 is smaller than 26.

## 22. How to sum a column and ignore errors?

The image above shows a formula in cell C9 that sums numbers in cell range C3:C7 and ignores errors.

Array Formula in cell C9:

### Explaining formula in cell C9

#### Step 1 - Replace errors with 0 (zero)

The IFERROR function lets you handle most formula errors with ease.

IFERROR(*value*, *value_if_error*)

IFERROR(C3:C7, 0)

becomes

IFERROR({5;#DIV/0!;6;#NAME?;2})

and returns

{5;0;6;0;2}

#### Step 2 - Sum values in array

SUM(IFERROR(C3:C7, 0))

becomes

SUM({5;0;6;0;2})

and returns 13 in cell C9. 5+6+2=13

## 23. Sum values based on a date

The image above shows a formula in cell C11 that sums numbers in cell range C3:C7 if dates in B3:B7 are equal to C10.

Array formula in cell C11:

I recommend the SUMIF function built exactly for this without entering the formula as an array formula.

### Explaining formula in cell C9

#### Step 1 - Logical expression

B3:B7=C10

becomes

{43831;43832;43833;43832;43835}=43832

and returns

{FALSE; TRUE; FALSE; TRUE; FALSE}

#### Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(B3:B7=C10, C3:C7, "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE}, C3:C7, "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 3; 5; 2; 4}, "")

and returns {""; 3; ""; 2; ""}.

#### Step 3 - Sum values in the array

SUM(IF(B3:B7=C10, C3:C7, ""))

becomes

SUM({""; 3; ""; 2; ""})

and returns 5. 3+2 = 5.

## 24. Sum values based on week number

The image above shows a formula in cell C11 that sums numbers in cell range C3:C7 if the corresponding weeks based on the dates in B3:B7 are equal to C10.

Array formula in cell C11:

### Explaining formula in cell C9

#### Step 1 - Calculate week numbers

The ISOWEEKNUM function calculates the number of the ISO week number of the year for a specific date.

ISOWEEKNUM(B3:B7)

becomes

ISOWEEKNUM({43839; 43838; 43835; 43833; 43837})

and returns {2; 2; 1; 1; 2}

#### Step 2 - Logical expression

ISOWEEKNUM(B3:B7)=C10

becomes

{2; 2; 1; 1; 2}=1

and returns {FALSE; FALSE; TRUE; TRUE; FALSE}.

#### Step 3 - Evaluate IF function

IF(ISOWEEKNUM(B3:B7)=C10, C3:C7, "")

becomes

IF({FALSE; FALSE; TRUE; TRUE; FALSE}, C3:C7, "")

becomes

IF({FALSE; FALSE; TRUE; TRUE; FALSE}, {1; 3; 5; 2; 4}, "")

and returns {""; ""; 5; 2; ""}

#### Step 4 - Sum values in the array

SUM(IF(ISOWEEKNUM(B3:B7)=C10, C3:C7, ""))

becomes

SUM({""; ""; 5; 2; ""})

and returns 7. 5+2 = 7.

## 25. Sum values by month

The image above shows a formula in cell C11 that sums numbers in cell range C3:C7 if the corresponding weeks based on the dates in B3:B7 are equal to C10.

Array formula in cell C11:

### Explaining formula in cell C9

#### Step 1 - Calculate number representing the position of the month in a year

The MONTH function extracts the month as a number from an Excel date.

1 - January, 2 - February, 3 - March, 4 - April, 5 - May, 6 - June, 7 - July, 8 - August, 9 - September, 10 - October, 11 - November, 12 - December

MONTH(B3:B7)

becomes

MONTH({43839; 43869; 43895; 43864; 43837})

returns

{1; 2; 3; 2; 1}

#### Step 2 - Logical expression

MONTH(B3:B7)=C10

becomes

{1; 2; 3; 2; 1}=1

and returns {TRUE; FALSE; FALSE; FALSE; TRUE}

#### Step 3 - Evaluate IF function

IF(MONTH(B3:B7)=C10, C3:C7, "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE}, {1; 3; 5; 2; 4}, "")

and returns {1; ""; ""; ""; 4}

#### Step 4 - Sum values in the array

SUM(IF(MONTH(B3:B7)=C10, C3:C7, ""))

becomes

SUM({1; ""; ""; ""; 4})

and returns 5. 1 + 4 = 5.

## 26. Sum values by year

The image above shows a formula in cell C11 that sums numbers in cell range C3:C7 if the corresponding weeks based on the dates in B3:B7 are equal to C10.

Array formula in cell C11:

### Explaining formula in cell C9

#### Step 1 - Calculate year based on an Excel date

The YEAR function returns the year from an Excel date.

YEAR(B3:B7)

becomes

YEAR({44205; 43869; 44260; 43864; 44568})

and returns {2021; 2020; 2021; 2020; 2022}

#### Step 2 - Logical expression

YEAR(B3:B7)=C10

becomes

{2021; 2020; 2021; 2020; 2022}=2020

and returns {FALSE; TRUE; FALSE; TRUE; FALSE}

#### Step 3 - Evaluate IF function

IF(YEAR(B3:B7)=C10, C3:C7, "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 3; 5; 2; 4}, "")

and returns {""; 3; ""; 2; ""}

#### Step 4 - Sum values in array

SUM(IF(YEAR(B3:B7)=C10, C3:C7, ""))

becomes

SUM({""; 3; ""; 2; ""})

and returns 5. 3 + 2 = 5.

## 27. Sum values between two dates

The image above shows a formula in cell C12 that sums numbers in cell range C3:C7 if dates in B3:B7 are less than or equal to C11 and greater than or equal to C10.

Array formula in cell C12:

I recommend the SUMIFS function built exactly for this without entering the formula as an array formula.

### Explaining formula in cell C9

#### Step 1 - First logical expression

C10<=B3:B7

becomes

43832<={43831; 43832; 43833; 43834; 43835}

and returns

{FALSE; TRUE; TRUE; TRUE; TRUE}

#### Step 2 - Second logical expression

C11>=B3:B7

becomes

43834>={43831;43832;43833;43834;43835}

and returns

{TRUE; TRUE; TRUE; TRUE; FALSE}

#### Step 3 - Multiply arrays

(C10<=B3:B7)*(C11>=B3:B7)

becomes

{FALSE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; FALSE}

and returns {0; 1; 1; 1; 0}

#### Step 4 - Evaluate IF function

IF((C10<=B3:B7)*(C11>=B3:B7), C3:C7, "")

becomes

IF({0; 1; 1; 1; 0}, C3:C7, "")

becomes

IF({0; 1; 1; 1; 0}, {1; 3; 5; 2; 4}, "")

and returns {""; 3; 5; 2; ""}

#### Step 5 - Sum values in the array

SUM(IF((C10<=B3:B7)*(C11>=B3:B7), C3:C7, ""))

becomes

SUM({""; 3; 5; 2; ""})

and returns 10. 3+5+2 = 10.

The file is a *.xlsm file (macro-enabled Excel file), it contains a small macro that demonstrates how to use the SUM function in a VBA macro.

### Articles with the 'SUM' Function

The following 14 articles have formulas that contain the SUM function.

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Count multiple text strings in a cell range

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]

### Functions in 'Math and trigonometry'

The SUM function function is one of many functions in the 'Math and trigonometry' category.

Converts negative numbers to positive numbers, in other words, the ABS function removes the sign. Excel Function Syntax ABS(number) Arguments […]

The ACOS function calculates the arccosine, or inverse cosine, of a number. Formula in cell C3: =ACOS(B3) Excel Function Syntax […]

The ACOSH function calculates the inverse hyperbolic cosine of a number. Formula in cell C3: =ACOSH(B3) Excel Function Syntax ACOSH(number) […]

The ACOT function calculates the inverse cotangent of a number. The returned angle is given in radians from 0 (zero) […]

The ACOTH function calculates the inverse hyperbolic cotangent of a number. Formula in cell C3: =ACOTH(B3) Excel Function Syntax ACOTH(number) […]

How to use the AGGREGATE function

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]

The ASIN function calculates the arcsine of a number. The angle is in radians and is between -pi/2 to pi/2. =ASIN(B3) Excel […]

The ASINH function calculates the inverse hyperbolic sine of a number. =ASINH(B3) Excel Function Syntax ASINH(number) Arguments number Required. Any […]

The ATAN function calculates the arctangent of a number. The returned angle is in radians between -pi/2 to pi/2. =ATAN(B3) Excel […]

The ATAN2 function calculates the arctangent of an angle using specific x- and y-coordinates. The returned angle is in radians […]

The ATANH function calculates the inverse hyperbolic tangent of a number. =ATANH(B3) Excel Function Syntax ATANH(number) Arguments number Required. Must be […]

The BASE function converts a number into a text representation with a given radix (base). Formula in cell C3: =BASE(10, […]

How to use the CEILING function

The CEILING function rounds a number up to its nearest multiple. The number is rounded down if both the number […]

How to use the COMBIN function

The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]

How to use the COMBINA function

The COMBINA function calculates the number of combinations for a given number of elements from a larger group of elements. Formula in […]

The COS function calculates the cosine of an angle. Formula in cell C3: =COS(B3) Excel Function Syntax COS(number) Arguments number […]

The COSH function calculates the hyperbolic cosine of a number. Formula in cell C3: =COSH(B3) Excel Function Syntax COSH(number) Arguments […]

The COT function calculates the cotangent of an angle specified in radians. Formula in cell C3: =COT(B3) Excel Function Syntax […]

The COTH function calculates the hyperbolic cotangent of a hyperbolic angle. The function has been available since Excel 2013. Formula […]

The CSC function calculates the cosecant of an angle (radians). The CSC function returns the same value as 1/SIN(number). Formula in […]

How to use the DECIMAL function

The DECIMAL function converts a text representation of a number in a given base into a decimal number. Formula in […]

How to use the DEGREES function

The DEGREES function calculates degrees from radians. Formula in cell C3: =DEGREES(B3) Excel Function Syntax DEGREES(angle) Arguments angle Required. The […]

The EVEN function rounds a number up to the nearest even whole number. An even number is a number that […]

Returns e raised to the power of a number, e equals 2.71828182845904. Example, e^2 equals 7.389056099 Formula in cell C3: […]

The FACT function returns the factorial of a number. Formula in cell C3: =FACT(B3) Example, 3! = 3*2*1 = 6 […]

How to use the FACTDOUBLE function

The FACTDOUBLE returns the double factorial of a number. Formula in cell C3: =FACTDOUBLE(B3) Excel Function Syntax FACTDOUBLE(number) Arguments number […]

How to use the FLOOR.MATH function

The FLOOR.MATH function rounds a number down to the nearest integer or to the nearest multiple of significance. Formula in […]

How to use the FLOOR.PRECISE function

The FLOOR.PRECISE function rounds a number down to the nearest integer or nearest multiple of significance. Excel Function Syntax FLOOR.PRECISE(number, […]

The GCD calculates the greatest common divisor that divides all given arguments without a remainder. Formula in cell D3: =GCD(B3:B4) Excel […]

Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to […]

The LCM function calculates the least common multiple. The least common multiple is the smallest positive integer that is a […]

The LN function calculates the natural logarithm of a number. Natural logarithms are based on the constant e. Formula in […]

The LOG function calculates the logarithm of a number to a specific base. Formula in cell C3: =LOG(10) Excel Function […]

The LOG10 function calculates the logarithm of a number using the base 10. Formula in cell C3: =LOG10(B3) Excel Function […]

How to use the MINVERSE function

The MINVERSE function calculates the inverse matrix for a given array. This function is useful for solving equations with multiple variables. […]

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]

The Mod function returns the remainder after a number is divided by a divisor. The Mod function is short for […]

How to use the MROUND function

The MROUND function calculates a number rounded to a given multiple. Formula in cell D3: =MROUND(B3,C3) Excel Function Syntax MROUND(number, […]

How to use the MULTINOMIAL function

The MULTINOMIAL function calculates the ratio of the factorial of a sum of values to the product of factorials. Formula […]

The MUNIT function calculates the identity matrix for a given dimension. Array formula in cell B2: =MUNIT(3) The formula above returns an […]

The PI function returns the number pi (¶). Formula in cell B3: =PI() Excel Function Syntax PI() Arguments PI function […]

The POWER function calculates a number raised to a power. Excel Function Syntax POWER(number, power) Arguments number Required. The number […]

How to use the PRODUCT function

The PRODUCT function returns the product of the numbers given in the argument. Formula in cell D3: =PRODUCT(B3:C3) Excel Function […]

How to use the QUOTIENT function

The quotient function returns the integer portion of a division. Example, 5/2 = 2.5. The integer is 2. Excel Function […]

How to use the RADIANS function

The RADIANS function converts degrees to radians. Formula in cell C3: =RADIANS(B3) Excel Function Syntax RADIANS(angle) Arguments angle Required. The […]

The RAND function calculates a random real number greater than or equal to 0 and less than 1. The function […]

How to use the RANDARRAY function

The RANDARRAY function returns a table of random numbers across rows and columns. It is located in the Math and […]

How to use the RANDBETWEEN function

Returns a random whole number between the numbers you specify. This function is volatile meaning a new random number is […]

The ROUND function calculates a rounded number based on the number of digits you specify. Formula in cell D3: =ROUND(B3, […]

How to use the ROUNDDOWN function

The ROUNDDOWN function calculates a number rounded down based on the number of digits to which you want to round […]

How to use the ROUNDUP function

The ROUNDUP function calculates a number rounded up based on the number of digits to which you want to round […]

The SEC function calculates the secant of an angle. Formula in cell E3: =SEC(B3) Excel Function Syntax SEC(number) Arguments number […]

The SECH function calculates the hyperbolic secant of an angle. Formula in cell C3: =SECH(B3) Excel Function Syntax SECH(number) Arguments […]

How to use the SEQUENCE function

The SEQUENCE function creates a list of sequential numbers to a cell range or array. It is located in the […]

How to use the SERIESSUM function

The SERIESSUM function calculates the sum of a power series based on the following formula: SERIESSUM(x, n, m, a) = […]

The SIGN function returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) […]

The SIN function calculates the sine of an angle. Formula in cell C3: =SIN(B3) Excel Function Syntax SIN(number) Arguments number […]

The SINH function calculates the hyperbolic sine of a number. Formula in cell C3: =SINH(B3) Excel Function Syntax SINH(number) Arguments […]

The SQRT function calculates the positive square root. Formula in cell C3: =SQRT(B3) Excel Function Syntax SQRT(number) Arguments number Required. […]

The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]

The SUMIF function sums values based on a condition. The condition can be applied to the values being summed or […]

How to use the SUMIFS function

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

The SUMSQ function calculates the sum of the squares of the arguments. Formula in cell D3: =SUMSQ(B3:B5) 2^2 = 4, […]

How to use the SUMX2MY2 function

The SUMX2MY2function calculates the sum of the difference of squares of corresponding values in two arrays. Formula in cell F3: […]

How to use the SUMX2PY2 function

The SUMX2PY2 function calculates the sum of the sum of squares of corresponding values in two arrays. The sum of […]

How to use the SUMXMY2 function

The SUMXMY2 function calculates the sum of squares of differences of corresponding values in two arrays. Formula in cell F3: […]

The TAN function calculates the tangent of an angle. Formula in cell C3: =TAN(B3) Excel Function Syntax TAN(number) Arguments number […]

The TANH function calculates the hyperbolic tangent of a number. Formula in cell C3: =TANH(B3) Excel Function Syntax TANH(number) Arguments number […]

The TRUNC function removes the fractional part of the number to an integer. Formula in cell C3: =TRUNC(B3) Excel Function […]

Learn how to use the SUBTOTAL function

The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas […]

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form