SUM function in Excel

Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.

How to sum a total in multiple Excel tables

To sum a total in multiple tables, you can use the SUM function and structured references to refer to the columns to sum. See example below: Formula =SUM(Table1[column],Table2[column]) Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error. Explanation In the example shown, the formula in I6 is:… read more »

How to create dynamic reference table name in Excel

To build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. Formula =SUM(INDIRECT(table&”[column]”)) Note: INDIRECT is a volatile function and can cause performance issues in larger, more complex workbooks. Explanation In the example shown, the formula in L5 is: =SUM(INDIRECT(K5&”[Amount]”)) Which returns the SUM of Amounts… read more »

How to create running total in an Excel Table

This tutorial shows illustrates a Running total in Excel Table. To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference. Formula =SUM(INDEX([column],1):[@column]) Explanation  In the example shown, the formula in F5 is: =SUM(INDEX([Total],1):[@Total]) When copied down the column, this formula will return a running total… read more »

How to reference named range different sheet in Excel

This tutorials shows how to reference a named range on another sheet. To achieve this, you can use the INDIRECT function with the required sheet syntax. Formula INDIRECT(“‘”&sheet&”‘!”&name) Explanation In the example shown, the formula in D6 is: =SUM(INDIRECT(“‘”&B6&”‘!”&C6)) Which returns the sum of the named range “data” on Sheet1. How this formula works The… read more »

Sum race time splits in Excel

If you need to add up (sum) up race time splits that are some combination of hours, minutes, and seconds, you can simply use the SUM function. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below. Formula =SUM(range) Explanation The… read more »

Excel Data validation don’t exceed total

Using the example below, this tutorial shows how to create Data validation don’t exceed total in Excel. Formula =SUM(range)<=1000 Explanation To allow only values that don’t exceed a set sum, you can use data validation with a custom formula based on the SUM function. In the example shown, the data validation applied to B5:B9 is: =SUM($C$6:$C$9)<=1000 How… read more »

IF with boolean logic in Excel

This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested… read more »

If cell begins with x, y, or z in Excel

This tutorial shows how to calculate If cell begins with x, y, or z in Excel using the example below; Formula =SUM(COUNTIF(A1,{“x*”,”y*”,”z*”}))>0 Explanation To test values to see if they begin with one of several characters (i.e. begin with x, y, or z) , you can use the COUNTIF function together with the SUM function. In the… read more »

Sum range with INDEX in Excel

This tutorial shows how to calculate Sum range with INDEX in Excel using the example below;  Formula =SUM(INDEX(data,0,column)) Explanation To sum all values in a column or row, you can use the INDEX function to retrieve the values, and the SUM function to return the sum.  This technique is useful in situations where the row or column being summed… read more »

Lookup entire column in Excel

This tutorial shows how to Lookup entire column in Excel  using the example below; Formula =INDEX(data,0,MATCH(value,headers,0)) Explanation To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is: =INDEX(C5:F8,0,MATCH(I5,C4:F4,0)) Note: this formula is embedded… read more »

Sidebar