Monday, 15 July 2013

Excel Count Functions-Count,CountA,CountBlank,Dcount Functions Syntax and Examples

Excel COUNT Function
The COUNT  function is used to find the count of  Numbers in a data range.This function does not count the Text and Blank cells.

Syntax :
=COUNT(DataRange)

Here , The DataRange is the Range of Data from which you want to find the count. This function counts Numbers , Text cells and ignores the Blank  cells in a range.

COUNTA Function:
The COUNTA  function is used to find the count of  Non Blank cells in a data range. 

Syntax :
=COUNTA(DataRange)

COUNTBLANK Function:
The COUNTBLANK  function is used to find the count of  only Blank cells in a data range. 

Syntax :
=COUNTBLANK(DataRange)

Example :
The following example shown in the image covers above three functions.

Prod_Id
Formulas
Result
1234
 =COUNT(A1:A21)
5
ABC_1234
 =COUNTA(A1:A21)
18
ABC_1234
 =COUNTBLANK(A2:A21)
3

2345
BCD_2345
BCD_2345

3456
CDE_3456
CDE_3456
CDE_3456
4567
DEF_4567
DEF_4567
DEF_4567

5678
EFG_5678
EFG_5678

DCount Function :
The DCOUNT function In Microsoft Excel,  returns the number of cells in a column or database that contains numbers and meets a given criteria.

Syntax :
DCOUNT( range, field, criteria )

Range -  is the range of cells that you want to apply the criteria against.

Field - is the column to count the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks.

Criteria - is the range of cells that contains your criteria.

Example :
Suppose we have the data in excel at Range '$A$1:$D$6' , and the Criteria is at the Range '$A$9:$B$10' as Shown below

Order_ID
Quantity
Unit_Cost
Total Cost
1234567
3
25
75
1234568
6
10
60
1234569
2
35
70
1234570
5
15
75
1234571
4
20
80
Count based on Criteria
Quantity
Unit_Cost
DCOUNT Formula
Result
<5
 <=25
 'Count Specified Column Name
=DCOUNT($A$1:$D$6,"Order_ID",$A$9:$B$10)
2
<5
 <=25
 'Count Specified Column Number
 =DCOUNT($A$1:$D$6,1,$A$9:$B$10)
2



Note:
The Dcount Function as we discussed counts only numbers which meets the criteria , so that every time I take only 'Marks' Column , which is the only numbers column here to find count.
Student Id column is also a Numbers column but we do not specify any criteria on that.

Thanks.,
Tamatam


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts