How to sum a cell range
The easiest way to sum a cell range is to simply select the cell range and read the values in the status bar. It shows the total, the count of non-empty cells and the average.
In fact, you can customize the status bar to show even more data:
Here is how to show these calculations automatically in the status bar.
- Press with right mouse button on on the status bar with your mouse.
- Press with left mouse button on "Numerical Count", "Minimum", and "Maximum", see image below.
The image below demonstrates these calculations enabled.
Sum a column using a formula
This is probably the most common task in Excel and luckily, there is an easy short cut to use.
- Select the cell range you want to sum.
- Press and hold Alt on your keyboard.
- Then press =
This will create a formula containing the SUM function and a cell reference to the selected cells, see image above.
You can also go to tab "Home" on the ribbon and press with left mouse button on "AutoSum" button and get the same result. To create totals below all columns select cell range C13:N13 and press and hold Alt and then press =
Sum a given column using a formula
The picture above shows a formula in cell C15 that sums a column in cell range C3:N12 based on the specified column header in cell C14.
Formula in cell C15:
Explaining formula in cell C15
The MATCH function returns a number representing the position of the given value in cell C14, in C2:N2.
MATCH(C14, C2:N2, 0)
becomes
MATCH("May", {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0)
and returns 5.
The INDEX function returns the entire column in cell C3:N12 if the row argument is 0 (zero).
INDEX(C3:N12, 0, MATCH(C14, C2:N2, 0))
becomes
INDEX(C3:N12, 0, 5)
and returns {61; 68; 13; 19; 69; 96; 5; 7; 14; 50}.
The SUM function adds the numbers given and returns a total.
SUM(INDEX(C3:N12, 0, MATCH(C14, C2:N2, 0)))
becomes
SUM({61; 68; 13; 19; 69; 96; 5; 7; 14; 50})
and returns 402.
Sum a given row using a formula
Formula in cell C15:
This formula is very similar to the prior one, no explanation is needed.
Sum using an Excel defined Table
This image displays the dataset converted to an Excel defined Table. When you press with left mouse button on and drag to create cell references they are instantly changed to structured references, this means that you generally don't have to adjust the cell references when you add data to the Excel defined Table.
Formula in cell C15:
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
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 […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Related articles VLOOKUP and return […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]
This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use 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.