The syntax for the two functions is explained below: SUMIF searches the range using only one criterion, while SUMIFS can be used with as many as 127 criteria/conditions pairs. The difference between the two functions is given by the number of criteria used. This means that you can take a range and only add up certain cells, while completely ignoring the rest. SUMIF and SUMIFS functions add more functionalities to the basic SUM formula by introducing selection criteria. For example, you can type =SUMSQ(2, 3, A8, C11:C20) and the function will still work correctly. Just like the SUM function you can use values, cells, arrays, or a mix of those. In this example we have taken cells L9, L10, L11, and L12 and used the formula =SUMSQ(L9:L12). Using SUMSQ we can easily calculate the square values from cells or range and sum all the values. If you want to add up all the values from a column you cannot use the Autosum option since it will not return the correct value. This means that if you have an empty cell on your column Excel will only take into account the last range starting from the empty cell and ending at the bottom of the column. Keep in mind that this only works with continuous ranges. The Autosum option is available in the Home tab of the Ribbon, in the Editing section. You can also make a selection of the cells you want to add up and press the button.
For this, all you need to do is select the cell underneath the last value on the column and press Autosum. This is when more advanced and versatile functions like SUMIF or SUMPRODUCT come into play.Īnother quick way to sum all the cells from a table is to use the Autosum option from the Excel menu. This means that you can easily select the wrong cell or miss one that should have been included. However, this method is prone to human errors since it requires you to manually select the salaries of employees working in IT. As you can see you can combine cells with ranges in the same SUM formula. In this case, instead of a single range we just picked the cells we were interested in and skipped the rest of the divisions. The result is the same as in Example 1, the only difference is the method used for achieving this.Īdditionally, in our file, we also calculated the total annual salaries for employees working in IT. For this example, we have named G2:G25 as “Salaries” and used the formula =SUM(Salaries) to calculate the total annual salaries.
You can also name a range of cells and use that name in the SUM function. Then close the brackets (parenthesis) and hit Enter. To select the range you just need to select G2 and with your left mouse click pressed drag all the way down to G25. We can easily achieve this by using the SUM function: Let’s assume we need to calculate the salaries of all employees.