Difference between revisions of "Manuals/calci/SUMIF"
Jump to navigation
Jump to search
| (12 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | <div | + | <div style="font-size:30px">'''SUMIF (Array,Condition,SumArray)'''</div><br/> |
| + | where | ||
| + | *<math>Array</math> is the range of cells that is evaluated by the specified criteria, | ||
| + | *<math>Condition</math> is a condition to evaluate in the form of a number, expression or text, and | ||
| + | *<math>SumArray</math> is the actual range of cells to be added. | ||
| + | **SUMIF(), adds the cells specified by a given criteria. | ||
| − | + | == Description == | |
| − | |||
| − | |||
| − | + | SUMIF (Array,Condition,SumArray) | |
| − | + | For Example, | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | SUMIF([10,20,30],">15", [50,60,70]) ''returns 130'' | |
| − | ''' | + | In above example, Calci checks for the values > 15 in first array (20 and 30) and returns the sum of corresponding values in second array (60 and 70) and displays '''130''' as a result. |
| − | + | *Blank values in <math>Array</math> are ignored. | |
| + | *<math>Condition</math> can be in the form of a number, expression, a cell reference, function or text. Expression, function or text criteria should be written in double quotation marks (e.g. ">30" or "A2+A3<100" or "Banana"). | ||
| + | *Argument <math>SumArray</math> is an optional. It specifies the range of cells to add if the mentioned condition is satisfied. | ||
| + | *If <math>SumArray</math> is omitted, Calci adds the values in the same range for which criteria is applied. | ||
| + | *<math>SumArray</math> and <math>Array</math> can be of different sizes. The actual cell to be added are determined by using upper leftmost cell in <math>SumArray</math> as the beginning cell, till the cells corresponding in size and shape to <math>Array</math>. | ||
| + | *If <math>Condition</math> is not satisfied, Calci displays zero (0) as a result. | ||
| − | + | == Examples == | |
| − | < | + | <div id="6SpaceContent" class="zcontent" align="left"> |
| − | |||
| − | |||
| − | + | {| id="TABLE3" class="SpreadSheet blue" | |
| + | |- class="even" | ||
| + | | class=" " | '''Fruit''' | ||
| + | | class=" " | '''Quantity''' | ||
| + | | class=" " | '''Price''' | ||
| + | | class=" " | | ||
| − | + | |- class="odd" | |
| − | + | | class="sshl_f" | Orange | |
| − | + | | class="sshl_f" | 20 | |
| + | | class="sshl_f" | $10 | ||
| + | | class="sshl_f" | | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" | Apple | ||
| + | | class="sshl_f" | 10 | ||
| + | | class="sshl_f" | $15 | ||
| + | | class="sshl_f" | | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" | Banana | ||
| + | | class="sshl_f" | 15 | ||
| + | | class="sshl_f" | $5 | ||
| + | | class="sshl_f" | | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" | Mango | ||
| + | | class="sshl_f" | 10 | ||
| + | | class="sshl_f" | $15 | ||
| + | | class="sshl_f" | | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" | Pear | ||
| + | | class="sshl_f" | 15 | ||
| + | | class="sshl_f" | $20 | ||
| + | | class="sshl_f" | | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" | | ||
| + | | class="sshl_f" | | ||
| + | | class="sshl_f" | | ||
| + | | class="sshl_f" | | ||
| + | |} | ||
| − | + | =SUMIF(B2:B6,"10",C2:C6) : Checks for the fruits with Quantity equal to '10' and adds the corresponding prices. <br />Returns '''30''' as a result. | |
| + | =SUMIF(B2:B6,">10") : Adds up the quantity of fruits that are greater than '10'. Returns '''50''' as a result. | ||
| + | =SUMIF(A2:A6,"=Pear",C2:C6) : checks for the Pear fruit in the list and adds the price. Returns '''20''' as a result. | ||
| − | + | ==Related Videos== | |
| − | + | {{#ev:youtube|v=V1acCft4fzY|280|center|SUMIF}} | |
| − | |||
| − | + | ==See Also== | |
| − | + | ||
| − | + | *[[Manuals/calci/SUM | SUM]] | |
| − | + | *[[Manuals/calci/COUNTIF | COUNTIF]] | |
| − | + | ||
| − | + | ==References== | |
| − | + | *[http://en.wikipedia.org/wiki/Summation SUM] | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | *[[Z_API_Functions | List of Main Z Functions]] | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | | | ||
| − | + | *[[ Z3 | Z3 home ]] | |
| − | |||
| − | |||
Latest revision as of 15:35, 21 November 2018
SUMIF (Array,Condition,SumArray)
where
- is the range of cells that is evaluated by the specified criteria,
- is a condition to evaluate in the form of a number, expression or text, and
- is the actual range of cells to be added.
- SUMIF(), adds the cells specified by a given criteria.
is the range of cells that is evaluated by the specified criteria,
is a condition to evaluate in the form of a number, expression or text, and
is the actual range of cells to be added.
Description
SUMIF (Array,Condition,SumArray)
For Example,
SUMIF([10,20,30],">15", [50,60,70]) returns 130
In above example, Calci checks for the values > 15 in first array (20 and 30) and returns the sum of corresponding values in second array (60 and 70) and displays 130 as a result.
- Blank values in are ignored.
- can be in the form of a number, expression, a cell reference, function or text. Expression, function or text criteria should be written in double quotation marks (e.g. ">30" or "A2+A3<100" or "Banana").
- Argument is an optional. It specifies the range of cells to add if the mentioned condition is satisfied.
- If is omitted, Calci adds the values in the same range for which criteria is applied.
- and can be of different sizes. The actual cell to be added are determined by using upper leftmost cell in as the beginning cell, till the cells corresponding in size and shape to .
- If is not satisfied, Calci displays zero (0) as a result.
Examples
| Fruit | Quantity | Price | |
| Orange | 20 | $10 | |
| Apple | 10 | $15 | |
| Banana | 15 | $5 | |
| Mango | 10 | $15 | |
| Pear | 15 | $20 | |
=SUMIF(B2:B6,"10",C2:C6) : Checks for the fruits with Quantity equal to '10' and adds the corresponding prices.
Returns 30 as a result. =SUMIF(B2:B6,">10") : Adds up the quantity of fruits that are greater than '10'. Returns 50 as a result. =SUMIF(A2:A6,"=Pear",C2:C6) : checks for the Pear fruit in the list and adds the price. Returns 20 as a result.
Related Videos
See Also
References