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
View answer
=COUNTIF(C4:C10,"DELHI")
Answer will be 2
Q2
COUNT THE NUMBER OF NOIDA EMPLOYEES
View answer
=COUNTIF(C4:C10,"NOIDA")
Answer will be 3
Q3
COUNT THE NUMBER OF EMPLOYEES GETTING BASIC > 20000?
View answer
=COUNTIF(D4:D10,">20000")
Answer will be 2
Q4
COUNT THE NUMBER OF EMPLOYEES GETTING BONUS < 5000?
View answer
=COUNTIF(F4:F10,"<5000")
Answer will be 3
Q5
COUNT THE NUMBER OF EMPLOYEES PAYING ESI?
View answer
=COUNTIF(J4:J10,">0")
Answer will be 3
Q6
COUNT THE NUMBER OF EMPLOYEES WHOSE TDS DEDUCTED?
View answer
=COUNTIF(K4:K10,">0")
Answer will be 4
Q7
COUNT THE NUMBER OF EMPLOYEES WHOSE TDS DEDUCTED >1000?
View answer
=COUNTIF(K4:K10,">1000")
Answer will be 3
Q8
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BASIC >20000?
View answer
=COUNTIFS(C4:C10,"DELHI",D4:D10,">20000")
Answer will be 1
Q9
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BONUS <5000?
View answer
=COUNTIFS(C4:C10,"DELHI",F4:F10,"<5000")
Answer will be 0
Q10
COUNT THE NUMBER OF DELHI EMPLOYEES GETTING BASIC >20000 AND BONUS <5000?
View answer
=COUNTIFS(C4:C10,"DELHI",D4:D10,">20000",F4:F10,"<5000")
Answer will be 0
Q11
COUNT THE NUMBER OF NOIDA EMPLOYEES PAYING ESI?
View answer
=COUNTIFS(C4:C10,"NOIDA",J4:J10,">0")
Answer will be 2
Q12
COUNT THE NUMBER OF NOIDA EMPLOYEES PAYING ESI AND GETTING BONUS>2000?
View answer
=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 |
|
Formula of Countif =countif(Select Area,"Write Criteria") |
Formula of Countifs
=countifs(Select Area 1,"Write Criteria 1",Select Area 2,"Write Criteria 2") |