Changes

Manuals/calci/SUMIF (view source)

Revision as of 22:00, 20 February 2014

1,262 bytes added , 22:00, 20 February 2014

no edit summary

Line 1: Line 1: −

~~<div id~~=~~"16SpaceContent" align~~=~~"left"><div class="ZEditBox" align="justify">~~

+

=SUMIF(CriRange, Criteria, SumRange)=

−

~~'''SUMIF'''(a1:a4~~, ~~'''c'''~~, R)

+

where

+

*<math>CriRange</math> is the range of cells that is evaluated by the specified criteria,

+

*<math>Criteria</math> is a condition to evaluate in the form of a number, expression or text, and

+

*<math>SumRange</math> is the actual range of cells to be added.

−

~~'''a1:a4''' is~~ the ~~range~~ of ~~cells that you want evaluated by~~ the given criteria.

+

SUMIF() calculates the sum of values in the given range if the specified criteria is satisfied.

−

~~'''C''' is the criteria in the form of a number, expression, or text~~

+

== Description ==

−

~~'''R''' are the actual cells to add~~

+

SUMIF(CriRange, Criteria, SumRange)

−

~~C1 C2~~

+

For Example,

−

~~R1 1000 100~~

+

SUMIF([10,20,30],">15", [50,60,70]) ''returns 130''

−

~~R2 9000 300~~

+

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.

−

~~R3 8000 250~~

+

*Blank values in <math>CriRange</math> are ignored.

+

*<math>Criteria</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>SumRange</math> is an optional. It specifies the range of cells to add if the mentioned condition is satisfied.

+

*If <math>SumRange</math> is omitted, Calci adds the values in the same range for which criteria is applied.

+

*<math>SumRange</math> and <math>CriRange</math> can be of different sizes. The actual cell to be added are determined by using upper leftmost cell in <math>SumRange</math> as the beginning cell, till the cells corresponding in size and shape to <math>CriRange</math>.

+

*If <math>Criteria</math> is not satisfied, Calci displays zero (0) as a result.

−

~~R4 12000 500~~

+

== Examples ==

−

<~~nowiki>~~=~~SUMIF(C1R1:C1R4,~~"~~>5000~~"~~,C2R1:C2R4) is 1050</nowiki~~>

+

−

~~<div~~ id="~~1SpaceContent~~" class="~~zcontent~~" ~~align~~="~~left~~"~~><div>~~

+

{| id="TABLE3" class="SpreadSheet blue"

+

|- class="even"

+

| class=" " | '''Fruit'''

+

| class=" " | '''Quantity'''

+

| class=" " | '''Price'''

+

| class=" " |

−

~~{| id="TABLE1" class="SpreadSheet blue"~~

−

~~|- class="even"~~

−

~~| class=" SelectTD SelectTD" |~~

−

~~<div id="1Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="1Space_Copy" title="Click and Drag over to AutoFill other cells."></div>~~

−

~~| Column1~~

−

~~| class=" " | Column2~~

−

~~| class=" " | Column3~~

−

~~| class=" " | Column4~~

|- class="odd"

−

| class=" " | ~~Row1~~

+

| class="sshl_f" | Orange

−

| class="sshl_f " | ~~1000~~

+

| class="sshl_f" | 20

−

| class="sshl_f " | ~~100~~

+

| class="sshl_f" | $10

−

| class="sshl_f" |

+

| class="sshl_f" |

−

~~| class="sshl_f" |~~

+

|- class="even"

−

| class=" " | ~~Row2~~

+

| class="sshl_f" | Apple

−

| class="sshl_f" | ~~9000~~

+

| class="sshl_f" | 10

−

| class="sshl_f" | ~~300~~

+

| class="sshl_f" | $15

−

| class="sshl_f" |

+

| class="sshl_f" |

−

~~| class="sshl_f" |~~

+

|- class="odd"

−

~~| Row3~~

+

| class="sshl_f" | Banana

−

| class="sshl_f" | ~~8000~~

+

| class="sshl_f" | 15

−

| class="sshl_f" | ~~250~~

+

| class="sshl_f" | $5

−

| class="sshl_f" |

+

| class="sshl_f" |

−

| class="sshl_f" |

+

|- class="even"

−

~~| Row4~~

+

| class="sshl_f" | Mango

−

| class=" " | ~~12000~~

+

| class="sshl_f" | 10

−

| class="sshl_f " | ~~500~~

+

| class="sshl_f" | $15

−

| class="sshl_f" |

+

| class="sshl_f" |

−

| class="sshl_f" |

+

|- class="odd"

−

| class=" " | ~~Row5~~

+

| class="sshl_f" | Pear

−

| class="sshl_f" |

+

| class="sshl_f" | 15

−

| class="sshl_f" | ~~1050~~

+

| class="sshl_f" | $20

−

| class="sshl_f" |

+

| class="sshl_f" |

−

~~| class="sshl_f" |~~

+

|- class="even"

−

~~| Row6~~

+

| class="sshl_f" |

−

| class="sshl_f" |

+

| class="sshl_f" |

−

| class="sshl_f" |

+

| 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. 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.

+

==See Also==

+

*[[Manuals/calci/SUM | SUM]]

+

*[[Manuals/calci/COUNTIF | COUNTIF]]

+

==References==

+

*[http://en.wikipedia.org/wiki/Summation SUM]

Swapna

writer

2,661

edits