Difference between revisions of "Manuals/calci/DCOUNT"

From ZCubes Wiki
Jump to navigation Jump to search
 
(7 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
=DCOUNT(db, fld, cri)=
+
<div style="font-size:30px">'''DCOUNT (Array,Field,Criteria)'''</div><br/>
−
 
+
*Where 'Array' is the range of cells that makes up the list or database
−
*Where 'db' 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 criteria.
−
*'fld' shows which column is used in the function and  
+
**DCOUNT() counts the numbers in the column of a list or database that match the specified criteria.
−
 
 
−
*'cri' is the range of cells that contains the specified criteria.
 
−
 
 
−
*DCOUNT() counts the numbers in the column of a list or database that match the specified criteria.
 
  
 
== Description ==
 
== Description ==
  
−
DCOUNT(db, fld, cri)
+
DCOUNT (Array,Field,Criteria)
  
 
It counts the cells that contain numbers in a listed column or database that match the specified condition.
 
It counts the cells that contain numbers in a listed column or database that match the specified condition.
  
−
*The argument 'fld' is optional. If not mentioned, the function counts all the numbers from database that match the criteria.
+
*The argument 'Field' is optional. If not mentioned, the function counts all the numbers from database that match the criteria.
  
−
* A 'column name' in quotes (e.g. "Salary") or 'column number' (e.g. 3, if Salary is 3rd column) can be used as 'fld' argument.
+
* A 'column name' in quotes (e.g. "Salary") or 'column number' (e.g. 3, if Salary is 3rd column) can be used as 'field' argument.
  
 
*Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition.
 
*Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition.
Line 27: Line 23:
 
== Examples ==
 
== Examples ==
  
−
Consider the following table with Employee names, Age and Salary columns.  
+
Consider the following table in ZCalci with Employee names, Age and Salary columns.  
 
Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.
 
Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.
−
<!--
 
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
−
 
−
DCOUNT
 
−
 
−
</div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left"> 
 
−
 
−
[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 45 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="#0000ff">I.e =DCOUNT(C1R4:C3R10,”Salary”,C1R1:C2R3) is 3</font>]
 
−
 
−
<br /><br />
 
−
 
−
-->
 
  
 
<div id="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
Line 97: Line 30:
 
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class="sshl_f " | '''Name'''
+
| class="sshl_f" |
 +
| class="sshl_f" | A
 +
| class="sshl_f" | B
 +
| class="sshl_f" | C
 +
| class="sshl_f" | D
 +
|- class="odd"
 +
| class="sshl_f "| 1
 +
| class="sshl_f "| '''Name'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Age'''
−
|- class="odd"
+
|- class="even"
−
| class="sshl_f" |
+
| class="sshl_f "| 2
 +
| class="sshl_f "|
 
| class="sshl_f" | &lt;40
 
| class="sshl_f" | &lt;40
 
| class="sshl_f" | &gt;5000
 
| class="sshl_f" | &gt;5000
 
| class="sshl_fSelectTD SelectTD " | &gt;32
 
| class="sshl_fSelectTD SelectTD " | &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>
 
<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="even"
+
|- class="odd"
−
| class="sshl_f" |
+
| class="sshl_f" | 3
 +
| class="sshl_f "|
 
| class="sshl_f " | &gt;30
 
| class="sshl_f " | &gt;30
 
| class="sshl_f" |  
 
| class="sshl_f" |  
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="odd"
+
|- class="even"
 +
| 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="even"
+
|- class="odd"
 +
| 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="odd"
+
|- class="even"
 +
| 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="even"
+
|- class="odd"
 +
| 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="odd"
+
|- class="even"
 +
| class="sshl_f "| 8
 
| class="sshl_f" | Bob
 
| class="sshl_f" | Bob
 
| class="sshl_f" | 45
 
| class="sshl_f" | 45
 
| class="sshl_f" | 9000
 
| class="sshl_f" | 9000
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="even"
+
|- class="odd"
 +
| 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="odd"
+
|- class="even"
 +
| class="sshl_f "| 10
 
| class="sshl_f" | Jill
 
| class="sshl_f" | Jill
 
| class="sshl_f" | 45
 
| class="sshl_f" | 45
Line 150: Line 99:
  
  
−
=DCOUNT(A4:C10,"Salary",A1:C2)  : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000. ''Displays 3'' as a result.
+
=DCOUNT(A4:C10,"Salary",A1:C2)  : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000. <br>Displays '''3''' as a result.
 +
 
 +
=DCOUNT(A4:C10,"Age",A1:C2)      : Counts the number of cells in the 'Age' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000. <br>Displays '''3''' as a result.
  
−
=DCOUNT(A4:C10,"Age",A1:C2)     : Counts the number of cells in the 'Age' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000. ''Displays 3'' as a result.
+
=DCOUNT(A4:C10,"Salary",A1:C3)   : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000 in addition to employees whose Age>30. <br>Displays '''8''' as a result.
  
−
=DCOUNT(A4:C10,"Salary",A1:C3)   : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000 in addition to employees whose Age>30. ''Displays 8'' as a result.
+
=DCOUNT(A4:C10,"Salary",A1:C2)-DCOUNT(A4:C10,"Salary",A1:D2)  : Counts the difference in the number of cells in the 'Salary' column for employees whose Age<40 and Salary>5000, and employees with 40<Age>32 and salary>5000, in the range A4 to C10. <br>Displays '''1 '''(3-2=1) as a result.
  
−
=DCOUNT(A4:C10,"Salary",A1:C2)-DCOUNT(A4:C10,"Salary",A1:D2)  : Counts the difference in the number of cells in the 'Salary' column for employees whose Age<40 and Salary>5000, and employees with 40<Age>32 and salary>5000, in the range A4 to C10. ''Displays 1 ''(3-2=1) as a result.
+
=DCOUNT(A4:C10,3,A1:C2)   : Counts the number of cells in the 3rd column ('Salary'), in the range A4 to C10, for the employees whose Age<40 and Salary>5000. <br>Displays '''3''' as a result.
  
−
=DCOUNT(A4:C10,3,A1:C2)  : Counts the number of cells in the 3rd column ('Salary'), in the range A4 to C10, for the employees whose Age<40 and Salary>5000. ''Displays 3'' as a result.
+
== Related Videos ==
 +
{{#ev:youtube|D_1-SavHX-M|280|center|DCOUNT}}
  
 
== See Also ==
 
== See Also ==
Line 168: Line 120:
 
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
 
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
 
*[http://en.wikipedia.org/wiki/Aggregate_function DCOUNT]
 
*[http://en.wikipedia.org/wiki/Aggregate_function DCOUNT]
 +
 +
 +
 +
 +
*[[Z_API_Functions | List of Main Z Functions]]
 +
 +
*[[ Z3 |  Z3 home ]]

Latest revision as of 13:45, 24 August 2018

DCOUNT (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 criteria.
    • DCOUNT() counts the numbers in the column of a list or database that match the specified criteria.

Description

DCOUNT (Array,Field,Criteria)

It counts the cells that contain numbers in a listed column or database that match the specified condition.

  • The argument 'Field' is optional. If not mentioned, the function counts all the numbers from database that match the criteria.
  • A 'column name' in quotes (e.g. "Salary") or 'column number' (e.g. 3, if Salary is 3rd column) can be used as 'field' argument.
  • Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition.
  • The Criteria columns should not overlap the list of data.
  • DCOUNTA() can be used to count the number of non-blank cells from a list of database that match a specified criteria.

Examples

Consider the following table in ZCalci 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


=DCOUNT(A4:C10,"Salary",A1:C2) : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000.
Displays 3 as a result.

=DCOUNT(A4:C10,"Age",A1:C2) : Counts the number of cells in the 'Age' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000.
Displays 3 as a result.

=DCOUNT(A4:C10,"Salary",A1:C3) : Counts the number of cells in the 'Salary' column, in the range A4 to C10, for the employees whose Age<40 and Salary>5000 in addition to employees whose Age>30.
Displays 8 as a result.

=DCOUNT(A4:C10,"Salary",A1:C2)-DCOUNT(A4:C10,"Salary",A1:D2) : Counts the difference in the number of cells in the 'Salary' column for employees whose Age<40 and Salary>5000, and employees with 40<Age>32 and salary>5000, in the range A4 to C10.
Displays 1 (3-2=1) as a result.

=DCOUNT(A4:C10,3,A1:C2) : Counts the number of cells in the 3rd column ('Salary'), in the range A4 to C10, for the employees whose Age<40 and Salary>5000.
Displays 3 as a result.

Related Videos

DCOUNT

See Also

References



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