Difference between revisions of "Manuals/calci/DCOUNT"
Jump to navigation
Jump to search
| (9 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | =DCOUNT( | + | <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 ' | + | *'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. |
| − | |||
| − | *' | ||
| − | |||
| − | *DCOUNT() counts the numbers in the column of a list or database that match the specified criteria. | ||
== Description == | == Description == | ||
| − | DCOUNT( | + | 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 ' | + | *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. | *Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition. | ||
| Line 25: | 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="2SpaceContent" class="zcontent" align="left"> | <div id="2SpaceContent" class="zcontent" align="left"> | ||
| Line 95: | 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=" | + | |- class="even" |
| − | | class="sshl_f" | | + | | class="sshl_f "| 2 |
| + | | class="sshl_f "| | ||
| class="sshl_f" | <40 | | class="sshl_f" | <40 | ||
| class="sshl_f" | >5000 | | class="sshl_f" | >5000 | ||
| class="sshl_fSelectTD SelectTD " | >32 | | class="sshl_fSelectTD SelectTD " | >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=" | + | |- class="odd" |
| − | | class="sshl_f" | | + | | class="sshl_f" | 3 |
| + | | class="sshl_f "| | ||
| class="sshl_f " | >30 | | class="sshl_f " | >30 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- 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=" | + | |- 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=" | + | |- 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=" | + | |- 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=" | + | |- 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=" | + | |- 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=" | + | |- class="even" |
| + | | class="sshl_f "| 10 | ||
| class="sshl_f" | Jill | | class="sshl_f" | Jill | ||
| class="sshl_f" | 45 | | class="sshl_f" | 45 | ||
| Line 147: | Line 98: | ||
|} | |} | ||
| − | |||
| − | =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 | + | =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,"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: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, | + | =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. |
| − | = | + | == Related Videos == |
| + | {{#ev:youtube|D_1-SavHX-M|280|center|DCOUNT}} | ||
== See Also == | == See Also == | ||
| Line 163: | 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
See Also
References