In this article, we will demonstrate how to use the Excel **IF **statement to increment by 1. We will also increment by 1 using the **COUNTIF **function, and by using the **IF **and **COUNTIF **functions combined, with both single and multiple criteria.

Let’s work through 3 examples to illustrate.

### Example 1 – Using the IF Function When the Value Changes in Another Column

Suppose we have a list of products in **column C**, and want to number the instances of each product name in **column D**. The products in **column C** are grouped by product name. In **column D**, the value should increment for each product until the product name in** column C** changes. The number increment should then start from 1 again for the next product name.

**Steps:**

- In cell
**D5**enter the formula below:

`=IF(C5<>C4,1,D4+1)`

- Drag down the
**Fill Handle**to cell**D13**.

The result is as in the image below.

### Example 2 – Using the COUNTIF Function to Increment by 1

In the previous method, we used the **IF **statement to increment the number by 1. Here, we’ll use **the COUNTIF function** instead to perform the same operation.

**Steps:**

- In cell
**D5**enter the following formula:

`=COUNTIF($C$5:C5,C5)`

- Use the
**Fill Handle**tool to apply the formula in the cells below.

The numbers for each product name are incremented correctly, as in the image below.

### Example 3 – Combining the IF and COUNTIF Functions

In this example, we will use both **IF **and **COUNTIF **functions combined to increment by 1, for both single and multiple criteria.

**3.1 – Using a Single Criteria**

In the dataset below, we will count the number of products coming from the “**North**” Region.

**Steps:**

- In cell
**E5**enter the following formula:

`= IF($D5 = "North", COUNTIF($D5:$D$5, "North"), "")`

- Use the
**Fill Handle**tool to apply the formula in the cells below.

**Column E **contains some blank cells due to the absence of the word “**North**” in the adjacent cells in **column D**.

__Formula Breakdown__

**$D5=”North”:**Checks if the value in cell**D5**is equal to “**North**“.**($D)**means that the column reference remains fixed.**COUNTIF($D5:$D$5, “North”), “”)**: If the condition in the**IF**statement is true (cell**D5**contains “**North**“), then the**COUNTIF**function is used to count the number of cells containing “**North**” within the range**$D5:$D$5**. Since this range is just a single cell (**$D5**), it will return**1**if**D5**contains “**North**“. If the condition in the**IF**statement is false (cell**D5**does not contain “**North**“), the formula returns an empty string (“”), meaning that the cell containing this formula will be blank if**D5**is not “**North**.”**IF($D5 = “North”, COUNTIF($D5:$D$5, “North”), “”):**Checks if cell**D5**contains the text “**North**.” If it does, it counts how many times “**North**” appears in the range**$D5:$D$5**. If**D5**is not “**North**,” it returns an empty string.

**3.2 – Using Multiple Criteria**

To use the **IF **and **COUNTIF **functions combined to increment by 1 for multiple criteria, we made a slight change in the dataset by adding a column for **Status**.

If the product **Status** is **Sold **we’ll add 1, and if the product is **Not Sold **then we’ll subtract 1. Moreover, if the product is **Partially Sold** then we’ll do nothing.

If the number in **column E** is rising, it means that the products have been sold; if the number is falling, it means that the the products have not been sold.

**Steps:**

- In cell
**E5**enter the following formula:

`=IF(D5="Partially Sold","",COUNTIF(D$5:D5,"Sold")-COUNTIF(D$5:D5,"Not Sold"))`

- Drag the
**Fill Handle**down to cell**E13**.

__Formula Breakdown__

**D5=”Partially Sold”,””:**Checks if cell**D5**contains the text**“Partially Sold”**. If it does, the**IF**function returns an empty string (“”) because we don’t want to include partially sold items in the count.**COUNTIF(D$5:D5,”Sold”):**Counts the number of cells in the range**D$5:D5**that contain the text “**Sold**.” The**$**sign before the row number ensures that the range always starts from cell**D5**and expands as you drag the formula down to other cells.**COUNTIF(D$5:D5,”Not Sold”):**Counts the number of cells in the range**D$5:D5**that contain the text “**Not Sold**“.**COUNTIF(D$5:D5,”Sold”)-COUNTIF(D$5:D5,”Not Sold”):**Subtracts the count of**“Not Sold”**items from the count of “**Sold**” items.

## Things to Remember

**Parentheses usage:**Parentheses are used to control the order in which Excel performs calculations. If you have more complex conditions or calculations within the**IF**statement, make sure to use parentheses correctly.**Using absolute cell references:**If you want certain references to stay constant while copying the formula, use absolute cell references with**dollar signs**($).**Using logical operators:**Excel provides various logical operators**( =, <>, >, <, >=, <=)**that can be combined to create complex conditions. These operators allow you to test multiple conditions within the**IF**statement.

## Frequently Asked Questions

**1. What if the cell contains a formula instead of a value? Will the IF statement still work?**

Yes, the **IF **statement works with formulas as well. If the cell contains a formula that returns a numeric value, the **IF **statement will treat it like any other number and increment it by 1.

**2. What are absolute and mixed cell references, and when should I use them?**

Absolute references **($A$1)** do not change when copied, while mixed references** ( $A1 or A$1)** allow either the row or column reference to change when copied. Use absolute references when you want to lock a reference to a specific cell while copying the formula.

**3. Will the IF statement work with negative numbers?**

Yes, the **IF **statement works with negative numbers just like positive numbers. You can use it to increment or decrement negative values based on specific conditions.

**4. Can I apply the IF statement to increment values in other units or measurements?**

Yes, as long as you understand the conversion and adjust the formula accordingly.

**Download Practice Workbook**

Get FREE Advanced Excel Exercises with Solutions!