The function COUNTIFS returns the number of rows found in your document for a specific lookup
- The column containing the first criterion
Select the column where is the first criterion
- First criterion
Write the value of the first criterion
- [optional] The column containing the second criterion
Select the column where is the second criterion
- [optional] Second criterion
Write the value of the second criterion
=COUNTIFS(Column with the first criteria, First criteria, Column with the second criteria, Second criteria, ....)
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.
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
sivaraman
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/
24/08/2023 @ 05:25
very good
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