You are here

Home

12 - COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS in Excel

Very useful formulas if we are interested in number of cells according to defined criteria.

Function  Count in Excel  = COUNT (Value1, Value2, ....) - returns the count of cells in selected area which contain numeric value

Excel Count text function = COUNTA (Value1, Value2, ....) - returns the count of cells in the selected area which are not empty

Excel Count empty cells  = COUNTBLANK  (Value1, Value2, ....) - returns the count of blank cells in the selected area 

 

Count if in Excel = COUNTIF (range, criteria) - returns the count of cells based on the specified criteria.

Example 1: in database count how many companies made ​​profit higher than € 5,000,000

The formula will look following: = COUNTIF (C2: C13, "> 5000000")

Excel Count function and Excel Countif function

Range - the range of cells showing the company's profit (D2: D13)

Criteria - criteria that we are interested in, in this case the profit "> 5000000"

 

Example 2: We are interested in how many companies from USA achieved higher profit than 5 000 000 €  - Countif multiple criteria function. 

Countifs in Excel = COUNTIFS (criteria_range1, Criteria1 [criteria_range2, criteria2] ......)

Excel Countif multiple criteria

criteria_range1 - the range of cells showing the company's profit

Criteria1 - criteria which we are interested in - Profit "> 5000000"

criteria_range2 - the range of cells showing Country of origin (B2: B13)

criteria2 - criteria which we are interested in - the "USA"

Application of COUNTIFS is same as in the case of COUNTIF  whereas formula allows to specify more fields and more related conditions. In other words COUNTIFS combines more COUNTIF formulas into one comprehensive formula.

Partner sites:

kosime.sk
Kosenie trávy, čistenie pozemkov a záhradnícke práce