Instead of Filters,we can also use Countif Formula

**
Formula of Countif
**

=countif(Select Area,"Write Criteria")

**
Note:-
**

Criteria here is always in inverted commas " "

####
**
Assignment
**

**
Resolve last question using countif and countifs
**

**
Q1
**
*
COUNT THE NUMBER OF DELHI EMPLOYEES
*

=COUNTIF(C4:C10,"DELHI")

Answer will be 2

**
Q2
**
*
COUNT THE NUMBER OF NOIDA EMPLOYEES
*

=COUNTIF(C4:C10,"NOIDA")

Answer will be 3

**
Q3
**
*
COUNT THE NUMBER OF EMPLOYEES GETTING BASIC > 20000?
*

=COUNTIF(D4:D10,">20000")

Answer will be 2

**
Q4
**
*
COUNT THE NUMBER OF EMPLOYEES GETTING BONUS < 5000?
*

=COUNTIF(F4:F10,"<5000")

Answer will be 3

**
Q5
**
*
COUNT THE NUMBER OF EMPLOYEES PAYING ESI?
*

=COUNTIF(J4:J10,">0")

Answer will be 3

**
Q6
**
*
COUNT THE NUMBER OF EMPLOYEES WHOSE TDS DEDUCTED?
*

=COUNTIF(K4:K10,">0")

Answer will be 4

**
Q7
**
*
COUNT THE NUMBER OF EMPLOYEES WHOSE TDS DEDUCTED >1000?
*

=COUNTIF(K4:K10,">1000")

Answer will be 3

**
Q8
**
*
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BASIC >20000?
*

=COUNTIFS(C4:C10,"DELHI",D4:D10,">20000")

Answer will be 1

**
Q9
**
*
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BONUS <5000?
*

=COUNTIFS(C4:C10,"DELHI",F4:F10,"<5000")

Answer will be 0

**
Q10
**
*
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BASIC >20000 AND BONUS <5000?
*

=COUNTIFS(C4:C10,"DELHI",D4:D10,">20000",F4:F10,"<5000")

Answer will be 0

**
Q11
**
*
COUNT THE NUMBER OF NOIDA EMPLOYEES PAYING ESI?
*

=COUNTIFS(C4:C10,"NOIDA",J4:J10,">0")

Answer will be 2

**
Q12
**
*
COUNT THE NUMBER OF NOIDA EMPLOYEES PAYING ESI AND GETTING BONUS>2000?
*

*
=COUNTIFS(C4:C10,"NOIDA",J4:J10,">0",F4:F10,">2000")
*

*
Answer will be 1
*

**
Download File and Try
**

Note

If there are more than one condition,we use Countifs instead of Countif

Formula of Countifs

=countifs(Select Area 1,"Write Criteria 1",Select Area 2,"Write Criteria 2")

**
DIFFERENCE BETWEEN COUNTIF AND COUNTIFS
**

COUNTIF | COUNTIFS |

it is used if there is only one condition | It is used if there is 2 or more conditions |

=countif(Select Area,"Write Criteria") |
Formula of Countifs
=countifs(Select Area 1,"Write Criteria 1",Select Area 2,"Write Criteria 2") |

Learn GST, TDS, Excel (with certification)