Excel SUM, SUMIF, SUMIFS Functions
Excel SUM, SUMIF, SUMIFS Functions sum the numbers that are in a specific column or row based on specific criteria. However, the SUMIF and SUMIFS functions have arguments and that is the main difference between them and the SUM function.
Excel SUM Function
The SUM function in Excel is used to add up the numerical values in a range of cells. For example, the formula SUM(A2:A10) sums all numbers that are in the range A2 through A10.
|
1 |
=SUM(A2:A10) |
The syntax of SUM Function is =SUM(number1, [number2], …). However, if you need to sum a range, then the syntax would simply be SUM(range).
Excel SUMIF Function
The SUMIF function in Excel is a mathematical function that calculates the sum of values in a specified range that meets certain criteria. For example, the formula SUMIF(A2:A10, “>10”) will sum only the values that are larger than 10. The syntax of SUMIF is =SUMIF(range, criteria,[sum_range]). The [sum_range] is an optional range you specify to sum only the values in the [sum_range] where the corresponding cells in the range are equal to the criteria. For example, =SUMIF(A2:A13, “Store B”, “B2:B13) will sum the values in B2 through B13 where the corresponding cells in the range A2:A13 contain the word “Store B”.
Excel SUMIFS Function
The SUMIFS function sums all numbers in a range of cells that meet single or multiple criteria. The syntax of SUMIFS function is: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
For example, you would use the SUMIFS function to sum the net sales of specific stores that are in a specific city. For example, a formula like =SUMIFS(C2:C13,A2:A13,”City A”,B2:B13,”Store B”) would sum the values in range C2 through C13 where the corresponding cells in the range A2 through A13 contain the word “City A” and the corresponding cells in the range B2 through B13 contains the word “Store B”.
In conclusion, all Microsoft Excel SUM, SUMIF, SUMIFS Functions sum the numbers that are in a range of cells except for the arguments that SUMIF and SUMIFS provide. You can also perform a quick shortcut to perform the SUM function by pressing ALT and = Keys simultaneously. Note that the SUM function in Excel automatically ignores the text values.
See also Microsoft Excel Count Function.
For more details, please see SUMIF Function published by Microsoft.