Practice COUNTIF & COUNTIFS Function Online in Excel
Watch: How to use COUNTIF & COUNTIFS Function in Excel?
What is COUNTIF Function?
In Excel, “COUNTIF” counts the number of cells within a range that meet a single specified criteria. Click here to Read Full Tutorial
What is COUNTIF Function Syntax?
=COUNTIF(Range, criteria)
Practice 1: Countif Function to count the cell based on one Criteria
- Click on G4: Click on the cell G4 where you want to use the formula.
- Type
=COUNTIF: In the formula bar, type=COUNTIF. - Select the Range: Click and drag to select the cells that you want to search through (for example, the “Sales Rep” column).
- Enter the Criteria: Type the value you want to count (for example, a specific sales rep’s name).
- Press Enter: Press the Enter key to see the result. The formula will count how many times the value appears in the selected range.
What is COUNTIFS Function?
The COUNTIFS function in Excel counts the number of cells that meet multiple criteria across multiple ranges. Click here to Read Full Tutorial
What is COUNTIFS Function Syntax?
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Practice 2: Countifs Function to count the cell based on multiple Criteria's in Excel
- Click on the cell where you want the result (e.g., G10).
- Type this formula: =COUNTIFS(.
- Select the First Range : Click and drag to highlight the first range (e.g., $B$2:$B$49, the “Sales Rep” column).
- Enter the First Condition : Type the condition (e.g., H3, which has “David”). Your formula will look like:
=COUNTIFS($B$2:$B$49, H3. - Select the Second Range : Add a comma, then highlight the second range (e.g., $C$2:$C$49, the “Country” column).
- Enter the Second Condition : Type the second condition (e.g., H9, which has “Australia”). The formula will look like:
=COUNTIFS($B$2:$B$49, H3, $C$2:$C$49, H9). - Press Enter to get the result.
Practice 3:Countifs Functions the cells between a Date Range or between a Range in Excel
- Click on the Cell: Select the cell for the result.
- Type the Formula: Type
=COUNTIFS(. - First Range and Condition: Highlight
$B$2:$B$49and set the condition to matchH2. - Second Range and Condition: Highlight
$A$2:$A$49, and add the condition">"&H10-1. - Third Range and Condition: Highlight
$A$2:$A$49again, and add"<"&I10+1. - Press Enter: The formula counts matching values based on the three conditions
Practice 4:Logical Operators in Countifs Function
- Click on the Cell: Select the cell for the result (e.g., D2).
- Type the Formula: Start typing
=COUNTIFS(. - Set the First Range: Highlight
$C$2:$C$49(“Country” column). - Enter the First Condition: Type
"*"&H12&"*"to check ifH12is found in$C$2:$C$49. - Set the Second Range: Add a comma, then highlight
$B$2:$B$49(“Sales Rep” column). - Enter the Second Condition: Type
"David"to count rows where the name is"David". - Press Enter: The formula counts rows where:
$C$2:$C$49containsH12.$B$2:$B$49equals"David".
Practice 5:Logical Operators in Countifs Function (Begins with)
- Click on the Cell: Select the cell where you want the result (e.g., D2).
- Type the Formula: Start typing
=COUNTIFS(. - Select the Range: Highlight
$C:$C(the column to check). - Enter the Condition: Type
"Sing"&"*"to count cells starting with “Sing”. - Press Enter: The formula counts matching cells.
Practice 6:Logical Operators in Countifs Function (CONTAINS WITH)
- Click on the Cell: Select where you want the result.
- Type the Formula: Type
=COUNTIFS(. - Set the Range: Type
$C:$Cto check the entire “C” column. - Enter the Condition: Type
"*"&"Pore"to find any cell containing “Pore”. - Press Enter: The formula counts how many cells in column C have “Pore”.
