Excel keyboard shortcuts for sum how to#

Excel keyboard shortcuts for sum how to#

Have a look at this article if you want to know how to set up Pivot Tables.

We’ve already published various articles about Pivot Table:

You can count, sum up, create averages and much more.You can summarize data by any of the fields in your table.Actually, with a Pivot Table you can do many more things: But anyway, a Pivot Table can add up values. If you just want to get a simple sum, a Pivot Table might be a little bit over the top. There can be hidden cells, rows or column within this range – they won’t be regarded in the result. The cell references containing the values to be added.For summing up values just type 9 (if you want to regard all cells) or 109 for the only visible cells. The SUBTOTAL formula has two parts (please compare to number 2 in the image above): There are a number of possible calculation methods and one of them is summing up the values. Now we concentrate on the only visible cells as it is the major difference to the other methods. Hidden cells – for example because they’ve been filtered – won’t be regarded. SUBTOTAL has two options: Regarding all cells or only visible cells. For getting a sum under conditions please take a look at the SUMIFS formula (method 5 above).A sum of the product of two columns can easily be done with a third column, which contains the product of the two original columns.For simple sums, go with the SUM formula.Number 4 in above picture has an example for adding a condition to the SUMPRODUCT formula: In this case the sum of all blue car prices will be returned.Īs the usage of SUMPRODUCT is comparatively difficult, it’s recommended to use other ways: It’s a little bit more complicated than just with SUMIFS. Furthermore SUMPRODUCT can regard criteria.SUMPRODUCT can multiply two columns (or rows) with each other and return the sum (number 3 in above picture).But it can do much more than just returning sums: Please refer to this article for more information on how to use the SUMIFS formula. It can be used for getting a sum of values but also for just looking up values – as long as those values have a numeric format. SUMIFS sums up all values under one or more conditions. SUMIFS is only available since Excel 2007 – but nowadays most people use Excel 2007 or above. That way, you can also combine conditions by adding another IF formula within the first IF formula. Instead of just pressing enter press Ctrl + Shift + Enter. Type the formula without the curly brackets. Let’s say, we want to know the sum of the prices of all blue cars in the picture on the right hand side. With such array formula you can also sum up values under a condition. If you are not familiar with SUMIFS continue with the next method – and forget about SUMIF… If you are familiar with SUMIFS, we recommend only using SUMIFS from now on. SUMIF works more a less the same way as SUMIFS but can only regard one condition at a time. If you hide cells in between or cells are invisible because you’ve set a filter, they are still regarded.Įxamples for SUM, SUBTOTAL, SUMPRODUCT and SUM(IF())īefore there was the SUMIFS formula, Excel provided a similar way for adding values under a condition: SUMIF (without ‘s’ in the end). Please note that the sum of all cells within the given cell range will be added. Also getting sums of larger cell ranges is possible: =SUM(A1:A5) will return of all cells A1, A2, A3, A4 and A5. The sum of the values in these two cells will be returned. Instead of hard coded values you can also refer to other cells, e.g.Write the “hard coded” values (although not recommended), e.g.Within the brackets you’ve got several options: The SUM() formula is extremely easy to use: Just type =SUM() into an empty cell or press the sum button on the right hand side of the Home ribbon. After that we will compare them by their most important characteristics. Let’s start by taking a look at each method to create sums in Excel separately.