Difference between revisions of "Manuals/calci/DSUM"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left">  <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''DSUM'''</font></font></font><font color="#48...")
 
 
(14 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left"> 
+
<div style="font-size:30px">'''DSUM (Array,Field,Criteria)'''</div><br/>
 +
*Where 'Array' is the range of cells that makes up the list or database
 +
*'Field' shows which column is used in the function and
 +
*'Criteria' is the range of cells that contains the specified condition.
 +
** DSUM(), adds the numbers in the field column of records in the database that match the criteria.
  
−
<font color="#484848"><font face="Arial, sans-serif"><font size="2">'''DSUM'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">(</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''db'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''fld'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''cri'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">)</font></font></font>
+
== Description ==
  
−
<font color="#484848"><font face="Arial, sans-serif"><font size="2">'''where db'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the range of cells that makes up the list or database,</font></font></font>
+
DSUM (Array,Field,Criteria)
  
−
<font color="#484848"><font face="Arial, sans-serif"><font size="2">fld shows which column is used in the function and </font></font></font>
+
*This function adds the values from the database that satisfy the given condition.
  
−
<font color="#484848"><font face="Arial, sans-serif"><font size="2">cri   is the range of cells that contains the conditions that we specify. </font></font></font>
+
*A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column).
 +
*'Criteria' can be any range on the Calci with minimum of one column and at least one row indicating the condition.
 +
*'Criteria' columns should not overlap the list of data.
  
−
</div>
+
== Examples ==
−
----
+
Consider the following table with Employee names, Age and Salary columns.
−
<div id="1SpaceContent" class="zcontent" align="left"> 
+
 +
Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.
  
−
<font color="#484848"><font face="Arial, sans-serif"><font size="2">Adds the numbers in a column of a list or database that match conditions that you specify.</font></font></font>
 
−
 
−
</div>
 
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
−
 
−
DSUM
 
−
 
−
</div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left"> 
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#666666"><font face="Arial, sans-serif"><font size="2">Let's see an example</font></font></font>]
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">Let's see an example</font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">C1 C2 C3 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R1 Name Age Salary </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R2 &lt;30 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R3 &gt;40 &gt;5000</font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R4 Name Age Salary</font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R5 John 34 5500 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R6 Bill 35 6500 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R7 Clark 29 7000 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R8 Bob 43 9000</font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R9 Susan 37 4000</font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">R10 Jill 45 8000 </font>]
 
−
 
−
<font color="#0000ff"><br /></font>
 
−
 
−
[javascript:ToggleDiv('divExpCollAsst_4') <font color="#666666"><font face="Arial, sans-serif"><font size="2">I.e =DSUM(C1R4:C3R10,”Age”,C2R1:C3R3) is 117</font></font></font>]
 
−
 
−
</div>
 
−
----
 
−
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
 
−
----
 
−
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
 
−
----
 
−
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
 
−
----
 
 
<div id="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
  
 
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class="   " |
+
| class="sshl_f" |  
−
<div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>
+
| class="sshl_f" | A
−
| class="   " | Column1
+
| class="sshl_f" | B
−
| class="   " | Column2
+
| class="sshl_f" | C
−
| class=" " | Column3
+
| class="sshl_f" | D
−
| class=" " | Column4
+
 
 
|- class="odd"
 
|- class="odd"
−
| class=" " | Row1
+
| class="sshl_f" | 1
−
| class="sshl_f   " | Name
+
| class="sshl_f" | '''Name'''
−
| class="sshl_f " | Age
+
| class="sshl_f" | '''Age'''
−
| class="sshl_f" | Salary
+
| class="sshl_f" | '''Salary'''
−
| class="sshl_f" | 117
+
| class="sshl_f" | '''Age'''
 +
 
 
|- class="even"
 
|- class="even"
−
| class=" " | Row2
+
| class="sshl_f" | 2
 
| class="sshl_f" |
 
| class="sshl_f" |
−
| class="sshl_f" | &lt;30
+
| class="sshl_f" | &lt;40
−
| class="sshl_f" |
+
| class="sshl_f" | &gt;5000
−
| class="sshl_f           SelectTD SelectTD" |
+
| class="sshl_f" | &gt;32
−
<div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>
+
 
 
|- class="odd"
 
|- class="odd"
−
| Row3
+
| class="sshl_f" | 3
 
| class="sshl_f" |
 
| class="sshl_f" |
−
| class="sshl_f " | &gt;40
+
| class="sshl_f" | &gt;30
−
| class="sshl_f   " | &gt;5000
+
| class="sshl_f" |  
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="even"
 
|- class="even"
−
| Row4
+
| class="sshl_f" | 4
−
| class="sshl_f       " | Name
+
| class="sshl_f" | '''Name'''
−
| class="sshl_f" | Age
+
| class="sshl_f" | '''Age'''
−
| class="sshl_f" | Salary
+
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="odd"
 
|- class="odd"
−
| class=" " | Row5
+
| class="sshl_f" | 5
 
| class="sshl_f" | John
 
| class="sshl_f" | John
 
| class="sshl_f" | 34
 
| class="sshl_f" | 34
 
| class="sshl_f" | 5500
 
| class="sshl_f" | 5500
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="even"
 
|- class="even"
−
| class="sshl_f" | Row6
+
| class="sshl_f" | 6
 
| class="sshl_f" | Bill
 
| class="sshl_f" | Bill
 
| class="sshl_f" | 35
 
| class="sshl_f" | 35
 
| class="sshl_f" | 6500
 
| class="sshl_f" | 6500
−
| class="sshl_f " |
+
| class="sshl_f" |
 +
 
 
|- class="odd"
 
|- class="odd"
−
| class="sshl_f" | Row7
+
| class="sshl_f" | 7
 
| class="sshl_f" | Clark
 
| class="sshl_f" | Clark
 
| class="sshl_f" | 29
 
| class="sshl_f" | 29
 
| class="sshl_f" | 7000
 
| class="sshl_f" | 7000
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="even"
 
|- class="even"
−
| class="sshl_f" | Row8
+
| class="sshl_f" | 8
 
| class="sshl_f" | Bob
 
| class="sshl_f" | Bob
−
| class="sshl_f" | 43
+
| class="sshl_f" | 45
 
| class="sshl_f" | 9000
 
| class="sshl_f" | 9000
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="odd"
 
|- class="odd"
−
| class="sshl_f" | Row9
+
| class="sshl_f" | 9
 
| class="sshl_f" | Susan
 
| class="sshl_f" | Susan
−
| class="sshl_f " | 37
+
| class="sshl_f" | 37
 
| class="sshl_f" | 4000
 
| class="sshl_f" | 4000
 
| class="sshl_f" |
 
| class="sshl_f" |
 +
 
|- class="even"
 
|- class="even"
−
| class="sshl_f" | Row10
+
| class="sshl_f" | 10
 
| class="sshl_f" | Jill
 
| class="sshl_f" | Jill
 
| class="sshl_f" | 45
 
| class="sshl_f" | 45
−
| class=" " | 8000
+
| class="ssh1_f" | 8000
 
| class="sshl_f" |
 
| class="sshl_f" |
 
|}
 
|}
  
−
<div align="left">[[Image:calci1.gif]]</div></div>
+
=DSUM(A4:C10,"Salary",C1:C2) : Calculates the sum in the range A4 to C10, from the cells in the Salary column for the employees with Salary>5000. Returns '''36000''' as a result.
−
----
+
 
 +
=DSUM(A4:C10,3,A1:D2)        : Calculates the sum in the range A4 to C10, from the cells in the third column (Salary) for employees with 40<Age>32 AND Salary>5000. Returns '''12000''' as a result.
 +
 
 +
=DSUM(A4:C10,"Salary",C4:C10): Calculates the sum in the range A4 to C10, from the cells in the Salary column for all the employees in the database. Returns '''40000''' as a result.
 +
 
 +
==Related Videos==
 +
 
 +
{{#ev:youtube|UhnVgvyO1DU|280|center|DSUM}}
 +
 
 +
== See Also ==
 +
 
 +
*[[Manuals/calci/DPRODUCT| DPRODUCT]]
 +
*[[Manuals/calci/DAVERAGE| DAVERAGE]]
 +
 
 +
==References==
 +
 
 +
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
 +
*[http://en.wikipedia.org/wiki/Aggregate_function DSUM]
 +
 
 +
 
 +
 
 +
*[[Z_API_Functions | List of Main Z Functions]]
 +
 
 +
*[[ Z3 |  Z3 home ]]

Latest revision as of 13:50, 24 August 2018

DSUM (Array,Field,Criteria)


  • Where 'Array' is the range of cells that makes up the list or database
  • 'Field' shows which column is used in the function and
  • 'Criteria' is the range of cells that contains the specified condition.
    • DSUM(), adds the numbers in the field column of records in the database that match the criteria.

Description

DSUM (Array,Field,Criteria)

  • This function adds the values from the database that satisfy the given condition.
  • A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column).
  • 'Criteria' can be any range on the Calci with minimum of one column and at least one row indicating the condition.
  • 'Criteria' columns should not overlap the list of data.

Examples

Consider the following table with Employee names, Age and Salary columns.

Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.

A B C D
1 Name Age Salary Age
2 <40 >5000 >32
3 >30
4 Name Age Salary
5 John 34 5500
6 Bill 35 6500
7 Clark 29 7000
8 Bob 45 9000
9 Susan 37 4000
10 Jill 45 8000

=DSUM(A4:C10,"Salary",C1:C2) : Calculates the sum in the range A4 to C10, from the cells in the Salary column for the employees with Salary>5000. Returns 36000 as a result.

=DSUM(A4:C10,3,A1:D2) : Calculates the sum in the range A4 to C10, from the cells in the third column (Salary) for employees with 40<Age>32 AND Salary>5000. Returns 12000 as a result.

=DSUM(A4:C10,"Salary",C4:C10): Calculates the sum in the range A4 to C10, from the cells in the Salary column for all the employees in the database. Returns 40000 as a result.

Related Videos

DSUM

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/DSUM&oldid=211236"