Monday, 22 July 2013

Excel Text Function to get Day,Month,Year Values from a Date Value

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


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts