Ms Excel tutorial of SUM, SUMIF, AutoSum and more function

Ms Excel tutorial of SUM, SUMIF, AutoSum and more function

The SUM characteristic in Excel provides the arguments you input in a components. as an example,

entering =SUM(10, 2) returns 12. This function subtracts whilst poor numbers are used within the arguments.

Ms Excel tutorial of SUM, SUMIF, AutoSum and more function
Ms Excel tutorial of SUM, SUMIF, AutoSum and more function

SUMIF characteristic you use the SUMIF function in Excel to sum the values in a range that meet criteria that you specify. as an example, if a column consists of numbers, you may sum most effective the values which can be large than five.

 Use AutoSum to sum numbers

Use AutoSum to sum numbers via clicking a button on the house tab (and on the system tab). AutoSum can sum either a column or a row of numbers.

 Summing up methods to add and rely Excel facts

Summing (including values) is an fundamental part of information analysis, whether or not you are subtotaling income within the Northwest area or doing a strolling total of weekly receipts. Excel gives more than one strategies that you can use to sum statistics. to help you make the nice desire, this text affords a …

how to accurate a #value! error in average or SUM features …

This subject matter gives assist for the maximum commonplace eventualities for the #cost! mistakes in the common or SUM functions.

 SUMIFS function

The SUMIFS characteristic, one of the math and trig capabilities, adds all of its arguments that meet a couple of standards. as an example, you would use SUMIFS to sum the quantity of outlets in the us of a who (1) live in a single zip code and (2) whose profits exceed a specific greenback price. This video is part …

Subtract numbers

Use the minus signal operator or the SUM function to subtract numbers in Excel.

Use Excel as your calculator

Use Excel as your calculator. … click on AutoSum > Sum. AutoSum will routinely feel the variety to be summed and construct the formula for you.

 SUBTOTAL function

this text describes the formulation syntax and usage of the SUBTOTAL feature in Microsoft Excel. Description. … The sum of the subtotal of the cells A2:A5, …

guidelines and examples of array formulation

An array components (one that spans a couple of cells) can do calculations on rows and columns of cells in which you may in any other case need to apply numerous formulas. for instance, you may rely the number of characters which can be contained in a number of cells, sum most effective numbers that meet positive situations (which includes the lowest values in a selection or numbers that …

the way to use sum function:

SUM feature

The SUM characteristic, one of the math and trig features, provides values. you may upload person values, cell references or levels or a mixture of all 3.

for instance:

•        =SUM(A2:A10)

•        =SUM(A2:A10, C2:C10)

SUMIF feature

you operate the SUMIF function to sum the values in a variety that meet standards which you specify. for instance, assume that in a column that consists of numbers, you want to sum simplest the values which might be larger than 5. you can use the following formulation: =SUMIF(B2:B25,”>five”)

hints:

•        if you need, you can observe the standards to one range and sum the corresponding values in a unique range. for example, the system =SUMIF(B2:B5, “John”, C2:C5) sums handiest the values in the variety C2:C5, in which the corresponding cells in the range B2:B5 identical “John.”

•        To sum cells based totally on multiple standards, see SUMIFS characteristic

Syntax

SUMIF(range, standards, [sum_range])

The SUMIF characteristic syntax has the following arguments:

•        range   Required. The range of cells which you want evaluated by standards. Cells in every range have to be numbers or names, arrays, or references that comprise numbers. clean and textual content values are unnoticed. the selected range can also comprise dates in preferred Excel layout (examples under).

•        standards   Required. The criteria within the shape of various, expression, a cellular reference, text, or a feature that defines which cells will be brought. for example, standards may be expressed as 32, “>32”, B5, “32”, “apples”, or these days().

vital: Any textual content standards or any standards that includes logical or mathematical symbols should be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks aren’t required.

•        sum_range   elective. The real cells to add, if you want to add cells other than those designated inside the range argument. If the sum_range argument is ignored, Excel adds the cells which might be exact in the variety argument (the identical cells to which the standards is implemented).

•        you could use the wildcard characters—the question mark (?) and asterisk (*)—because the standards argument. A query mark suits any single character; an asterisk suits any collection of characters. in case you need to discover an real query mark or asterisk, type a tilde (~) previous the person.

feedback

•        The SUMIF feature returns wrong effects while you use it to healthy strings longer than 255 characters or to the string #cost!.

•        The sum_range argument does no longer need to be the equal size and form because the range argument. The real cells that are brought are decided with the aid of the usage of the higher leftmost cellular in the sum_range argument as the start cellular, after which inclusive of cells that correspond in length and form to the range argument. for instance:

If range is    And sum_range is          Then the actual cells are

A1:A5          B1:B5 B1:B5

A1:A5          B1:B3 B1:B5

A1:B4 C1:D4         C1:D4

A1:B4 C1:C2          C1:D4

but, when the range and sum_range arguments inside the SUMIF feature do no longer incorporate the same quantity of cells, worksheet recalculation can also take longer than anticipated.

 

Examples

copy the instance facts within the following desk, and paste it in cellular A1 of a new Excel worksheet. For formulation to expose results, choose them, press F2, after which press enter. if you need to, you may regulate the column widths to see all the statistics.

Property Value Commission      Data

$100,000                                                $7,000                                  $250,000

$200,000                                                $14,000

$300,000                                                $21,000

$400,000                                                $28,000

Formula                                                Description                         Result

=SUMIF(A2:A5,”>160000″,B2:B5)           Sum of the commissions for property values over $160,000.                                               $63,000

=SUMIF(A2:A5,”>160000″)                     Sum of the property values over $160,000.    $900,000

=SUMIF(A2:A5,300000,B2:B5)                Sum of the commissions for property values equal to $300,000.                                               $21,000

=SUMIF(A2:A5,”>” & C2,B2:B5)              Sum of the commissions for property values greater than the value in C2.                              $49,000

Example 2

Replica the instance statistics within the following table, and paste it in mobile A1 of a brand new Excel worksheet. For formulation to expose effects, select them, press F2, after which press input. if you need to, you can regulate the column widths to see all of the records.

Category                                                   Food                                 Sales

Vegetables                                                 Tomatoes                          $2,300

Vegetables                                                 Celery                                $5,500

Fruits                                                           Oranges                             $800

Butter                                 $400

Vegetables                                                 Carrots                               $4,200

Fruits                                                           Apples                               $1,200

Formula                                                     Description                      Result

=SUMIF(A2:A7,”Fruits”,C2:C7)                     Sum of the sales of all foods in the “Fruits” category.                                                                   $2,000

=SUMIF(A2:A7,”Vegetables”,C2:C7)            Sum of the sales of all foods in the “Vegetables” category.                                                     $12,000

=SUMIF(B2:B7,”*es”,C2:C7)                          Sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples).             $4,300

=SUMIF(A2:A7,””,C2:C7)                              Sum of the sales of all foods that do not have a category specified.                                      $400

 

 

The SUMIFS characteristic provides all arguments that meet more than one standards

The SUMSQ characteristic sums more than one values after it performs a mathematical square operation on every of them

The COUNTIF function counts simplest the values that meet a unmarried standards

The COUNTIFS characteristic counts handiest the values that meet more than one criteria

IFS characteristic (office 365, Excel 2016 and later)

Evaluation of formulas in Excel

the way to keep away from broken formulation

Use error checking to stumble on mistakes in formulation

Math & Trig capabilities

Excel functions (alphabetical)

Excel features (via class)

the usage of SUMIF, COUNTIF, and related features for quick facts analysis (unfastened preview)

Use AutoSum to sum numbers

when you click on AutoSum, Excel robotically enters a formulation (that uses the SUM function) to sum the numbers.

here’s an instance. to add the January numbers in this amusement budget, pick out cellular B7, the mobile right now under the column of numbers. Then click on AutoSum. A system appears in cellular B7, and Excel highlights the cells you’re totaling.

Press input to show the end result (95.94) in cell B7. you could additionally see the system inside the components bar at the pinnacle of the Excel window.

Notes:

  • To sum a column of numbers, select the cell right now beneath the final wide variety inside the column. To sum a row of numbers, pick out the cell immediately to the proper.
  • AutoSum is in places: domestic > AutoSum, and formulation > AutoSum.
  • after you create a system, you could copy it to different cells as opposed to typing it time and again. for instance, if you reproduction the system in mobile B7 to cellular C7, the components in C7 automaticallyadjuststo the brand new region, and calculates the numbers in C3:C6.
  • you may also use AutoSum on more than one cellular at a time. as an instance, you may highlight both cell B7 and C7, click on AutoSum, and overall both columns at the identical time.
  • you may also sum numbers via creatinga easyformulation

Leave a Reply