Make summery Report using Countif & Countifs
How to use countif formula in excel
Countif Function use for Count given criteria from the large data.
If once apply the formula need not update report time to time. Value will update automatic according Data Entry.
In the Employee master data Staff Name, Designation & 3 department shown.
We are finding how many staff working in each department.
 |
| COUNTIF Formula & COUNTIFS formula Make Summery Report in MS Excel |
For Sumif function need range & Criteria.
Range means which column that contains finding data. Means for make department wise summery we need select department column D.
Each Department is our criteria.
Range (D:D)
Criteria (F5) or Text
In the cell G5 press =COUNTIF(
Then select Range coloumn D & press comma
then click criteria cell F5 (Text can use as criteria in the inverted comma Ex. “HR”)& Press comma
Close the bracket & Press Enter.
=COUNTIF(D:D,F5) or =COUNTIF(D:D,”HR”)
Copy that formula cell & paste in the cell bellow.
Make summery Report using Countifs
Countifs Formula
For Detailed Report making can use COUNTIF Finction.
Here we are finding how many staff working in each department & each designation.
We need 2 range & 2 criteria.
1st Rage is Designation column C & criteria is particular Designation F12.
2nd range is Department column D & criteria particular department G11.
In the cell G12 type =COUNTIFS(
Select 1st range press comma then 1st criteria press comma then Second range & criteria with comma & press Enter.
=COUNTIFS($C:$C,$F$12,$D:$D,G11)
You can use one or more criteria.
Important Note-
in the following image in report table Department criteria cell is on top of formula cell G11. When copy formula & paste in the cell bellow criteria also move down. There need to lock cell Cell G12. For lock cell click formula cell & press F2. click on G12 & press F4.
Now copy & Paste formula in cell bellow.
or Department criteria can use as text using Inverted comma Ex. “HR”
