The COUNTIFS() function in Excel can be used to count the number of rows in a spreadsheet that meet multiple criteria.
This function uses the following syntax:
COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, …)
where:
- criteria_range1: The first range of cells to look in.
- criterion1: The criterion to look for in the first range of cells.
The following example shows how to use this function in practice.
Example: Using COUNTIFS in Excel
Suppose we have the following dataset that shows the team and points scored for seven different basketball players:
We can use the following COUNTIFS() formula to count the number of players who play for the Lakers and who score 20 or more points per game:
=COUNTIFS(B2:B8, "Lakers", C2:C8, ">20")
The following screenshot shows how to use this formula in practice:
The total number of players that meet these two criteria is 2.
Specifically we can see that player “D” and “E” meet these two criteria.
Note that in this example we only used the COUNTIFS() function with two cell ranges, but we can use this function with any number of cell ranges that we’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
COUNTIF vs. COUNTIFS in Excel: What’s the Difference?
How to Use COUNTIF with OR in Excel
How to COUNTIF Greater Than But Less Than Some Number in Excel
Hi Zach, I have 4 “PLAYER’s” initials in Column A and their first name, last initial in Column B, in Alphabetical Order. Then I have the “PLAYER’s” initials again in a random order in Column C, labelling them as “BUYER’s”. Lastly, I how much the player’s paid to buy another player.
Example:
“PLAYER” / “BUYER” / BUY PRICE / Amt PLAYER paid as BUYER
PA Player A PB 5 20
PB Player B PC 10 5
PC Player C PD 15 10
PD Player D PA 20 15
I am trying to put an IF-THEN-ELSE formula into the “Amt PLAYER paid as BUYER” Column. The formula will look in Column C for someone’s initials, and IF they find them, THEN the adjacent dollar amount in column D will be inserted into the Column E cell, or ELSE a 0 will be inserted into the Column E cell. Here is how far I got with my formula and am getting stuck at the THEN part of my formula –> (D$2:D$6).
=IF(COUNTIF(C$2:C$6,A2),(D$2:D$6),0)
Hoping you can help complete it for me.
Hi Mike…Your formula is close, but the issue lies in how you’re referencing the adjacent dollar amount from column D. You cannot directly reference the range `(D$2:D$6)` in this context because Excel expects a specific value rather than a range for the `IF` function.
To correctly achieve what you want, you need to use the `INDEX` and `MATCH` functions to locate the corresponding value in column D when the initials in column A match a “BUYER” in column C.
Here’s the completed formula:
“`excel
=IF(COUNTIF(C$2:C$6,A2),INDEX(D$2:D$6,MATCH(A2,C$2:C$6,0)),0)
“`
### Explanation:
1. **`COUNTIF(C$2:C$6,A2)`**:
– Checks if the initials in column A (`A2`) exist in column C.
– Returns `TRUE` if found, `FALSE` otherwise.
2. **`MATCH(A2,C$2:C$6,0)`**:
– Finds the row number where the initials in column A (`A2`) match a “BUYER” in column C.
3. **`INDEX(D$2:D$6,…)`**:
– Retrieves the corresponding value from column D based on the row number returned by `MATCH`.
4. **`IF(…,INDEX(…),0)`**:
– If the initials are found, the formula returns the corresponding value from column D. If not, it returns `0`.
### How to Apply:
1. Copy the formula into `E2`.
2. Drag it down the column to apply to all rows.
### Expected Output:
For your example data:
– `PA` should get `20`.
– `PB` should get `5`.
– `PC` should get `10`.
– `PD` should get `15`.
This approach dynamically searches for matches and retrieves the correct amounts without manually entering values.