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