HOW TO USE COUNTIF IN EXCEL

HOW TO USE COUNTIF IN EXCEL

Excel COUNTIF function is best & so simple to use for situations when you want to count cells, based on a single condition. COUNTIF Function can be described as a formula that can be used for counting the number of cells that fulfill a particular condition, within a predefined range.

How to use COUNTIF in excel. Microsoft Excel defines COUNTIF as a formula that, “Counts the number of cells within a range that meet the given condition”.

This definition clearly explains that: COUNTIF Function is a better and sophisticated type of COUNT formula that gives you the control over, which cells you wish to count.

Syntax

=COUNTIF(range, criteria)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

countif1

This formula counts those cell which values are greater then 100.

The formula is  =COUNTIF(D2:D11,”>100″)

The result is 4. Because D2 to D11 four cells meet the condition which values are greater then 100. Hence this formula shows 4 in D12.

 

COUNT CHARACTER VALUE WITH COUNTIF FUNCTION


It’s possible and so easy to count character values in excel with COUNTIF function. The COUNTIF function is not case sensitive.

countif2

Here a simple COUNTIF formula which calculate the specific word or character in given range.

The formula is =COUNTIF(D3:D11,”MANAGER”)

 

The result is 2. Because D3 to D11 two cells meet the condition which values are manager. Hence this formula shows 2 in D11.

HOW TO USE MULTIPLE COUNTIF 


If you want to count multiple words from a column or row then you will have to use multiple COUNTIF function with different conditions.

The syntax is same :-

=COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)

=COUNTIF(C4:C12,”H.R”)+COUNTIF(C4:C12,”I.T”)+COUNTIF(C4:C12,”QUALITY”)

 

multicountif

The formula shows the result is 7. Because cell C4 to C12 the word “H.R” comes three times, “I.T” also comes three times and one time is “QUALITY”. Therefore 3+3+1=7

 

HOW TO USE COUNTIFS IN EXCEL


The COUNTIFS function Count cells that match multiple criteria.If you want to count based on multiple criteria, use COUNTIFS function.

Syntax

=COUNTIFS(range1, criteria1, [range2], [criteria2], …..)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

    countifs

=COUNTIFS(B3:B11,”ANKIT”,C3:C11,”I.T”)

The result is 2. Because the condition of formula met two times.

Leave a Reply

Your email address will not be published. Required fields are marked *