Function SUM, SUMIF And SUMIFS In MS Excel

Interneza - Function SUM, SUMIF And SUMIFS In MS Excel - Peace for all, we meet again with tutorials on MS Excel, after the last occasion on which I have discussed about Countif function. Well, on this occasion, let us discuss together about the function SUM, SUMIF And SUMIFS.

Function SUM, SUMIF and SUMIFS

Sure you already know that the excel program is devoted to data processing as well as fast and accurate calculations. However, to process data and calculations quickly in MS excel we need to know some of the existing functions, and such is the function SUM, SUMIF, and SUMIFS which we will consider together on this occasion. Ok, now we learn together about this function.

1. Function SUM

Sum function is a function used to calculate the total value of cell Example: = SUM (number1 [number2], [number3], [number4], ...) For instance, consider the following details:

 Learning-Function-SUM-SUMIF-And-SUMIFS-In-MS-Excel

The question is what is the total number of motor motorcycles in all the city, used to calculate the SUM function

How : Place the cursor in cell 14 type "= SUM (C1: C11)" (without the quotes) then press enter, then the result will be as shown below
 Learning-Function-SUM-SUMIF-And-SUMIFS-In-MS-Excel

2. Functions SUMIF

SUMIF function is used to calculate the total value of a cell with certain conditions Example: SUMIF (range, criteria, [Sum_range])

Still with the same sample data from the previous example, how the total number of motorcycles vixion in all cities ?. Place the cursor in cell D15 type "= SUMIF (C2: C12;" Motorcycles Vixion "; D2: D12)" (without the quotes). Then press enter.

The results are as shown below.
 Learning-Function-SUM-SUMIF-And-SUMIFS-In-MS-Excel

3. Functions SUMIFS

SUMIFS function is similar to SUMIF function but primarily the condition can be more than one.

Example: SUMIFS (Sum_range, criteria_range1, criteria, [criteria_range2, criteia2], ...)

With the same data from the previous example, what is the total number of motorcycles in Jakarta vixion ?. Place the cursor in cell D16, type "= SUMIFS (D2: D12; C2: C12;" Motorcycles Vixion "; B2: B12;" Jakarta ")" (without the quotes) and press enter.

The results are as shown below

 Learning-Function-SUM-SUMIF-And-SUMIFS-In-MS-Excel

Then what if the question converted into how many total motorcycle vixion Jakarta and Surabaya. To answer this question, the formula used is the same as the previous example, but with a slight modification in the criteria of the previous city there is only the city of Jakarta right now there is the city of Jakarta and Surabaya, to enter the city of Jakarta and Surabaya to use an array that is marked with braces, so it becomes { "Jakarta" Surabaya "}

so the formula should be typed as follows this "= SUMIFS (D2: D12; C2: C12;" Motorcycles Vixion "; B2: B12; {" Jakarta ";" Surabaya "})" (without the quotation marks) and press enter.

And the results are as shown below

 Learning-Function-SUM-SUMIF-And-SUMIFS-In-MS-Excel

Surely you are confused, why the result is still the same as the previous formula, is there something wrong? really did nothing wrong, because it uses a formula in the array then the result is an array as well, so the solution is to be calculated again by using the SUM function, and the formula is to be as follows.

"= SUM (SUMIFS (D2: D12; C2: C12;" Motorcycles Vixion "; B2: B12; {" Jakarta ";" Surabaya "}))" (without the quotes)

And the results are as shown below.
Belajar Fungsi SUM, SUMIF Dan SUMIFS Dalam MS Excel

That's about the SUM function. SUMIF and SUMIFS, how, you are confused ?. Please tweak it until you understand it. May be useful.Good Luck.


EmoticonEmoticon