SumIF ya...
Katanya yang buat, begini :
Excel <#> > Function reference <#> > Math and trigonometry <#>
SUMIF function
[image: Show All]Show All
[image: Hide All]Hide All
This article describes the formula syntax and usage of the *SUMIF*
function (function:
A prewritten formula that takes a value or values, performs an operation,
and returns a value or values. Use functions to simplify and shorten
formulas on a worksheet, especially those that perform lengthy or complex
calculations.) in Microsoft Office Excel.
Description
You use the *SUMIF* function to sum the values in a range (range: Two or
more cells on a sheet. The cells in a range can be adjacent or
nonadjacent.)that meet criteria that you specify. For example, suppose
that in a column
that contains numbers, you want to sum only the values that are larger than
5. You can use the following formula:
*=SUMIF(B2:B25,">5")*
In this example, the criteria is applied the same values that are being
summed. If you want, you can apply the criteria to one range and sum the
corresponding values in a different range. For example, the formula
*=SUMIF(B2:B5,
"John", C2:C5)* sums only the values in the range C2:C5, where the
corresponding cells in the range B2:B5 equal "John."
* Note * To sum cells based on multiple criteria, see SUMIFS
function<HA10047504.htm>
.
Syntax
SUMIF(range, criteria, [sum_range])
The *SUMIF* function syntax has the following arguments (argument: A value
that provides information to an action, an event, a method, a property, a
function, or a procedure.):
- *range* Required. The range of cells that you want evaluated by
criteria. Cells in each range must be numbers or names, arrays, or
references that contain numbers. Blank and text values are ignored.
- *criteria* Required. The criteria in the form of a number,
expression, a cell reference, text, or a function that defines which cells
will be added. For example, criteria can be expressed as 32, ">32", B5, 32,
"32", "apples", or TODAY().
*Important* Any text criteria or any criteria that includes logical or
mathematical symbols must be enclosed in double quotation marks (*"*).
If the criteria is numeric, double quotation marks are not required.
- *sum_range* Optional. The actual cells to add, if you want to add
cells other than those specified in the range argument. If the *sum_range
* argument is omitted, Excel adds the cells that are specified in the *
range* argument (the same cells to which the criteria is applied).
* Notes *
- The *sum_range* argument does not have to be the same size and shape
as the *range* argument. The actual cells that are added are determined
by using theupper leftmost cell in the *sum_range* argument as the
beginning cell, and then including cells that correspond in size and shape
to the *range* argument. For example: 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
- You can use the wildcard characters — the question mark (*?*) and
asterisk (***) — as the *criteria* argument. A question mark matches any
single character; an asterisk matches any sequence of characters. If you
want to find an actual question mark or asterisk, type a tilde (*~*)
preceding the character.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
[image: Show]How do I copy an example?
1. Select the example in this article.
*Important* Do not select the row or column headers.
[image: Selecting an example from Help]
Selecting an example from Help
2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.
*Important* For the example to work properly, you must paste it into
cell A1 of the worksheet.
5. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the
*Formulas*tab, in the
*Formula Auditing* group, click the *Show Formulas* button.
After you copy the example to a blank worksheet, you can adapt it to suit
your needs.
------------------------------
1 2 3 4 5 6
7
8
9
10 A B C *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
The example may be easier to understand if you copy it to a blank worksheet.
[image: Show]How do I copy an example?
1. Select the example in this article.
*Important* Do not select the row or column headers.
[image: Selecting an example from Help]
Selecting an example from Help
2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.
*Important* For the example to work properly, you must paste it into
cell A1 of the worksheet.
5. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the
*Formulas*tab, in the
*Formula Auditing* group, click the *Show Formulas* button.
After you copy the example to a blank worksheet, you can adapt it to suit
your needs.
------------------------------
1 2 3 4 5 6 7 8
9
10
11
12 A B C *Category* *Food* *Sales* Vegetables Tomatoes 2300 Vegetables
Celery 5500 Fruits Oranges 800
Butter 400 Vegetables Carrots 4200 Fruits Apples 1200 *Formula* *Description
* *Result* =SUMIF(A2:A7,"Fruits",C2:C7) Sum of the sales of all foods in
the "Fruits" category. 2000 =SUMIF(A2:A7,"Vegetables",C2:C7) Sum of the
sales of all foods in the "Vegetables" category. 12000
=SUMIF(B2:B7,"*es",C2:C7) Sum of the sales of all foods that end in "es"
(Tomatoes, Oranges, and Apples). 4300 =SUMIF(A2:A7,"",C2:C7) Sum of the
sales of all foods that do not have a category specified. 400
Wassalam,
Kid.
2013/5/17 ryan <[email protected]>
> **
>
>
> Mohon Infonya mengenai rumus sumif beserta analisa dan penalaran logika nya
>
>
>
