COUNTIFS is one of the most useful functions in Excel.
The function returns the number of rows found in your document for a specific lookup.
Don't count your rows with the filter ⛔⛔⛔
When you want to count the number of rows corresponding to specific criteria, many users use the Filter.
Why not for one or two research? But for more, it's a waste of time and you must use another technique.
The COUNTIFS function in Excel
The COUNTIFS function counts the number of rows corresponding to one or many criteria.
The COUNTIFS has been introduced since Excel 2007 and allows you to add between 1 and 255 criteria.
Arguments for COUNTIFS
The function COUNTIFS is very easy to use
- Select the first range of cells where is want to find your criteria
- Write your criteria
- Repro the steps for other criteria
=COUNTIFS(Range1,Criteria1,Range2,Criteria2,...)
COUNTIF vs COUNTIFS in Excel
There is no difference between these 2 functions in terms of performance. The only difference is the number of criteria you can fill.
- With COUNTIF you can count the rows for 1 criterion
=COUNTIF(Range1,Criteria1)
- With COUNTIFS you can count the rows for 255 criteria
=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,...)
The construction is the same, you just add more criteria 😉
Example to count rows with 2 criteria
How many single men are in your data set?
- Start by writing the formula COUNTIFS
=COUNTIFS(
- Select the range of cells where you want to search your criterion (the column C)
=COUNTIFS(C2:C20,
- Enter the value or the text, you want to search (here "Man").
=COUNTIFS(C2:C20,"Man",
The word "Man" is written between double quotes because it's a string and all strings must be written between double quotes in Excel.
Then, for the second criteria
- Select the range E2:E20
- Write the value "Single"
=COUNTIFS(C2:C20,"Man",E2:E20,"Single")
Watch your data ranges !!!
If the function COUNTIFS returns #VALUES!, is because you haven't selected the same range of cells for each criterion.
=COUNTIFS(C2:C20,"Man",E2:E21,"Single")
Replace the criteria with a reference
Instead of writing the value of the criteria directly in the formula, you can use the content of a cell by using the reference of the cell 😎😀
=COUNTIFS(C2:C20,H1,E2:E20,"Single")
Greater than / Lower than
You can create more complex criteria with logical symbols like greater than (>) or lower than (<). Have a look at this article to build your COUNTIFS function in this situation.
SAUL
Frédéric LE GUEN
PhilW
kazi
PARVATHY .V
27/06/2019 @ 22:06
if I Have created a formula for ranges let's say A1:B40, in one work sheet that extract data from a second worksheet,
when I received the new updated work sheet, new rows have been added, and my formulas will not cover them automatically
to be updated. is there a code to verify that no new rows have been added and if new rows where added the formula
automatically gets updated and instead of reading A1:B40 would increase the formula to cover the new rows A1:B120
28/06/2019 @ 13:40
If you put your data inside a Table, the references of your data will be automatically updated
https://www.excel-exercise.com/dynamic-sum-in-excel/
07/06/2019 @ 20:52
I have an array on one sheet (B2:F2002). I want to count how many instances of one number in any row is followed by a different number in the following row (i.e. how many times is a number 1 in a row of the array followed by a number 2 in the next row of the array). Is there a way to do this?
09/06/2019 @ 15:09
Not sure to understand your problem. Post your message on mrexcel.com. Probably someone will answer your question
26/12/2018 @ 11:58
Hi, the explanation is very nice and is very simple.
However, is it possible to provide the exercise sheet?
08/01/2019 @ 09:13
No, I don't give my exercises. That's my job too
19/01/2018 @ 05:50
THIS SIGHT WAS VERY USFULL LIKE ME NOT MORE EDUCATED THANK YOU