How to use SUM, SUMIF, SUMIFS functions in excel?

image_pdf

SUM function: The Excel SUM Function is used to add up the list of numeric values and returns its sum.

Download file: 

Syntax:

= SUM( number1, [number2], … )

Note: Numbers and dates are always counted as numeric values by the Excel Sum function. Where as the other text or logical values may be considered differently.

Example: SUM function will add the values in cell A1 and B1.

sumfunc

Or select the SUM option from the AutoSum dropdown. Here the range of cells is selected manually before selecting the option.

sumunc1

sumfunc1

SUMIF:
The Excel SUMIF function returns summation of selected range of cells if it satisfies the given criteria.
You can optionally select from which range you want to sum the value. Go through the below example to clearly understand it.

Syntax:

=SUMIF( range, criteria, [sum_range] )

Where range: The range of values to be tested against the given criteria.

Criteria: The condition to be tested against each of the values.

sum_range: It is optional argument and adds up the numeric values if the criteria is satisfied. If omitted then values from the range argument are used instead.

Note: if your criteria is a text string or an expression then it should be enclosed in double quotes. Also the Excel SUMIF function is not case-sensitive.

Example 1:

In the following example, we want to sum the total price of "Cooker"

For that select the range from A2:A8, set the criteria as "Cooker" and [sum_range] value is from the Price column which is C2:C8.

=SUMIF( range, criteria, [sum_range] )

sumif

Example 2:

Since we want to add up the prices of the items whose quantities are 2 or greater than 2 , we select the range of Quantity and apply the criteria.

=SUMIF( range, criteria, [sum_range] )

sumif1

SUMIFS: The Excel SUMIFS function is used when we want to apply more than one range which satisfies set of criteria and returns the corresponding values.

Syntax:

=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )

Where sum_range: A range of cell values which we want to add up if the criteria is satisfied.

criteria_range1: The range of cells which is to be tested against the criteria1. …(The supplied criteria_range arrays must all have the same length as the sum_range).

criteria1: The conditions to be tested against the values in criteria_range1.

Note: if your criteria is a text string or an expression then it should be enclosed in double quotes. Also the Excel SUMIFs function is not case-sensitive.

Example 1:

Summing up those items with name is "juicer" and has quantity 2.

=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )

sumifs

Example 2:

We have two criteria based on we want to sum the value in below example

1. Date of Manufacture should be after 09/09/2013. Required range: "C2:C8"

2. Price > 10,000. Required range: "D2:D8".

Result: You can see that no of items which followed above two criteria are 2, which is the quantity of Mixer grinder, Date of Manufacturer is 01-01-2014 and price is above 30,000.

=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )

sumifs1

 

 


 

7,986 total views, 1 views today

One thought on “How to use SUM, SUMIF, SUMIFS functions in excel?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>