## Monday, 22 July 2013

### How to get Day,Month and Year Values from a Date in Excel

Excel Text Function to retrieve Day,Month and Year Values from a Date Value
We can get the part of a Date value like Day , Day Name , Month, Month Name , Year values in Excel using the Text Function.

Go through the following example to understand how this stuff works. This is really very use full.

Example:

 DATE REQUIREMENT FORMULA RESULT 7/25/2015 Day Value =TEXT(A2,"dd") 25 Day Name =TEXT(A2,"dddd") Saturday Month Value =TEXT(A2,"mm") 07 Month Name =TEXT(A2,"mmmm") July Year Value =TEXT(A2,"yyyy") 2015 Day and Month =TEXT(A2,"dd/mm") 25/07 Month and Year =TEXT(A2,"mm-yyyy") 07-2015 Month Name and Year =TEXT(A2,"mmmm,yyyy") July,2015

Thanks,TAMATAM

## Wednesday, 17 July 2013

### Excel COUNTIF Function Syntax and Example

How find Count based on a specific Criteria in Excel
CountIF is a function which used to find the count of a particular value (how many times it repeated in the given data) in the given array or range of data.

Syntax:
=CountIF(Array,Criteria)

Here:
• Array is a list or range of data where you want to find the count based on Criteria.
• Criteria is a condition based on which you want to find count.
Example:

 Data Criteria COUNTIF Formula Result 125 ">=50" =COUNTIF(\$A\$1:\$A\$10,">=50") 2 345_End "*End" =COUNTIF(\$A\$1:\$A\$10,"*End") 3 50 "345*" =COUNTIF(\$A\$1:\$A\$10,"345*") 2 45 345_Start 123_End 25 10 5_End

Note:
If the value that you want to count is not existed in given range of data then its count is “0”.

Thanks,

TAMATAM

## Monday, 15 July 2013

### DCount Function in Excel

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.,

TPR

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