In this article, we are going to show you how to use Excel **IF **statement for increment by 1. Along with the **IF **statement, we will also discuss the **COUNTIF **function and the uses of **IF **and **COUNTIF **functions combinedly.

You will learn how to use those Excel functions in your practical life while working with Excel files. Moreover, you can apply the **IF **statement for single and multiple criteria according to your use. You can save time and streamline your workflow by effectively using the **IF **statement.

The Excel IF statement to increment by 1 is important for conditionally updating values, like tracking progress or counting occurrences, based on specific criteria. It’s useful in tasks such as tallying scores or monitoring inventory levels.

**Download Practice Workbook**

If you want a free copy of the illustrated workbook we discussed during the presentation, please click the link below this section.

**Table of Contents**hide

## How to Use Excel IF Statement for Increment by 1: 3 Practical Examples

We will explore 3 Examples to use Excel IF statement increment by 1. Let’s check those.

### 1. Use IF Function When Value Changes in Another Column

Suppose, you have a list of products in **column C**, and now you want to increment the number by 1 in **column D**. The value of **column D** increases until the value of** column C** changes. Then the number increment starts from 1 again. Now follow the below instructions to execute this by using **the IF statement**:

- Select cell
**D5**and then apply the below formula.

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

- Then drag down the
**Fill Handle**up to cell**D13.**

So you will able to see the like the image below.

### 2. Apply COUNTIF Function to Increase Number by 1

In the previous method, we used the **IF **statement to increase the number by 1. Here, we are going to use **the COUNTIF function** instead of the **IF **function to increase the number by 1. Now follow the below steps to increase the number by 1:

- Select cell
**D5**and then apply the following formula.

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

- Use the
**Fill Handle**tool to apply the formula for the entire column in the table.

### 3. Combine IF and COUNTIF Functions

In the earlier methods, we discussed using **IF **and **COUNTIF **functions separately to increase a number by 1. In this section, we will learn how to use Excel **IF **and **COUNTIF **statements combinedly for increment by 1. We will apply it for single and multiple criteria.

**Example 1: For Single Criteria**

Here we will discuss how to use Excel **IF **and **COUNTIF **statements combinedly for increment by 1 for single criteria. In the dataset below, we will try to count the number of products coming from the “**North**” Region. To calculate this follow the below instructions:

- Select cell
**E5**and then apply the following formula.

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

- If you observe the image below, you can see some blank cells in
**column E**due to the absence of the word “**North**” in the adjacent cells.

__Formula Breakdown__

**$D5=”North”:**This part of the formula 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 “**North**” within the range**$D5:$D$5**. Since this range is just a single cell (**$D5**), it will either 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**(“”)**. This means that the cell containing this formula will be blank if**D5**is not “**North**.”**IF($D5 = “North”, COUNTIF($D5:$D$5, “North”), “”):**This formula 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.

**Example 2: For Multiple Criteria**

In the previous method, we told about single criteria. Now we will be discussing how to use Excel **IF **and **COUNTIF **statements combinedly for increment by 1 for multiple criteria. We made a slight change in the dataset. We added selling status in the dataset.

If the product status is **Sold **then +1, and if the product is **Not Sold **then subtract 1. Moreover, if the product is **Partially Sold** then do nothing. So you can keep tracking the selling status easily by just observing the numbers.

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

Now follow the procedures below:

- Select cell
**E5**and write down the following formula

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

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

__Formula Breakdown__

**D5=”Partially Sold”,””:**This part of the formula 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”):**This part of the formula uses the**COUNTIF**function to count 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”):**Similar to the previous**COUNTIF**function, this part 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”):**The formula subtracts the count of**“Not Sold”**items from the count of “**Sold**” items.

## Things to Remember

**Parentheses usage:**Be careful while putting parentheses. 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, you can use the **IF **statement to increment values in other units or measurements, as long as you understand the conversion and adjust the formula accordingly.

## Conclusion

After reading the article, now you have a comprehensive overview of how to utilize Excel’s **IF **statement to increment values by 1. You also learned 3 effective methods that will help you immensely while working with Excel files.

Hope you enjoyed this article. You can write your suggestions or any queries in the comment section below. Lastly, visit the **ExcelDemy **website to learn more exciting features and capabilities of Excel.