If Cell Contains Value Then Multiply Using Excel Formula (3 Examples)

Multiplying values is one of the most commonly used Microsoft Excel operations, so it’s no wonder that there are a few different ways to do it. The multiply sign (*) is the simplest method of solving multiplication in Excel. You may swiftly multiply values, cells, entire columns, and rows with this method. But if a cell doesn’t contain values it will not multiply. In this article, we will demonstrate an Excel formula if the cell contains a value then multiply.


Download Practice Workbook

You can download the workbook and practice with them.


3 Different Examples to Multiply Using Formula If Cell Contains Value in Excel

To multiply cells that contain the value, we are using the following dataset. The dataset contains small companies, sales of the first 5 days in different months, but the company was not able to sell every day in each month. Now, suppose we want to multiply those sales which contain only some value to figure the total revenues so businesses may see how they’re doing financially. The amount of sales is an important indicator of a company’s health. It enables us to monitor the effectiveness of marketing initiatives, assess the efforts of sales personnel, and select the ideal locations for real stores.

3 Different Examples If Cell Contains Value Then Multiply Using Excel Formula


1. Apply PRODUCT Function to Multiply If Cell Contains Value

The PRODUCT function contains the combination of all the values passed in as arguments. When we need to multiply multiple cells simultaneously, the PRODUCT function comes in handy. The multiply (*) mathematical operator can also be used to execute the same action to multiply the cells containing values, but if it couldn’t find any value in a particular cell, it will give an error. The PRODUCT function will multiply the cells that contain value and if any cell contains nothing then it will ignore the cell automatically.

To use this function we are going to use the dataset below. The dataset indicates sales in January in column B, sales in February in column C and sales in March in column D. Now, we want to multiply the sales in column E.

For this, let’s follow the steps down and use the excel formula if the cell contains a value then multiply.

STEPS:

  • Firstly, select the cell where you want to put the formula to multiply the cells that contain the value. So, we select cell E5.
  • Secondly, enter the below formula into that selected cell.
=PRODUCT(B5,C5,D5)
  • Thirdly, press Enter to insert the formula into your spreadsheet data.

  • This will multiply only those cells which contain values. In this case, cell B5 and cell D5 contain value but cell C5 doesn’t contain any value. So the product function will only multiply cell B5 and cell D5 and show the result in cell E5.
  • Further, to copy the data over the range, drag the Fill Handle down. Or, instead of doing this, just double-click on the Plus (+) sign to AutoFill the column.

Apply PRODUCT Function to Multiply If Cell Contains Value

  • And, that’s it! Finally, we will be able to see in column E, that the cells only containing value will multiply.

Apply PRODUCT Function to Multiply If Cell Contains Value

Read more: How to Multiply One Cell by Multiple Cells in Excel (4 Ways)


Similar Readings


2. Excel Formula with IF Function to Multiply Only Value Containing Cells

One of Excel’s most common built-in functions is the IF function, which allows you to create logical comparisons between a number and what you expect. As a result, an IF expression can have two outcomes. If the comparative is True, the first result is True; if the comparison is False, the second result is False.

To apply the IF function to multiply cells containing values, we are using a similar dataset as before.

Excel Formula with IF Function to Multiply Only Value Containing Cells

For this, we need to follow some procedures. Let’s look at the steps down.

STEPS:

  • To begin, choose the cell where you want the if conditional formula to be entered. As a result, we choose cell E5.
  • Second, type the formula below into the selected cell.
=IF(B5="",1,B5) * IF(C5="",1,C5) * IF(D5="",1,D5)*IF(B5&C5&D5="",0,1)
  • Finally, hit the Enter key.

Excel Formula with IF Function to Multiply Only Value Containing Cells

  • You’ll now be able to analyze the data in the selected cell, as well as the formula in the formula bar.
  • Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.

  • That’s all there is to it! Finally, in column E, we can observe the multiplication of the cells which contain values.

🔎 How Does the Formula Work?

  • IF(B5=””,1,B5) * IF(C5=””,1,C5) * IF(D5=””,1,D5): This will compare if the condition is true or false and show the result. Inside the double quotation (“”) there is no content means the cell has nothing, and 1 means this will fulfill the condition.

→ OUTPUT: 22*1*25 = 550

  • IF(B5&C5&D5=””,0,1): This will compare the condition again if there are any cells that fulfill the condition or not.

→ OUTPUT: 1

  • IF(B5=””,1,B5) * IF(C5=””,1,C5) * IF(D5=””,1,D5)*IF(B5&C5&D5=””,0,1): This will finally show the result.

→ OUTPUT: 550*1 =550

Read More: How to Make Multiplication Table in Excel (4 Methods)


3. Combine IF and ISBLANK Functions to Multiply Cell If Cell Contains Value in Excel

The ISBLANK function is known as the IS functions collectively. If the value parameter is a reference to an empty cell, the ISBLANK function shows TRUE; otherwise, it returns FALSE. To multiply cells that contain the value, we are going to use the dataset down. The dataset shows a company’s cells in the first few days in three months. But as we can see there is no sale in the month of February. To ignore the month which doesn’t contain any value we can use the combination of the IF function and ISBLANK function.

Let’s follow the steps below.

STEPS:

  • First, to multiply the cells containing values, choose the cell into which the formula will be typed. As a consequence, we have decided to go with cell E5.
  • Second, type the formula below into the selected cell.
=IF(ISBLANK(C5), 1, C5)*B5*D5
  • Finally, press the Enter key to finish the procedure.

Combine IF and ISBLANK Functions to Multiply Cell That Contains Value

  • The result, as well as the formula in the formula bar, will now appear in the selected cell.
  • After that, to copy the formula across the range, drag the Fill Handle down. Alternatively, double-click the Plus (+) sign to AutoFill the range.

  • And, for the time being, that’s all there is to it! Finally, column D displays the multiplication of cells containing values and ignores the blank cells.

🔎 How Does the Formula Work?

  • ISBLANK(C5), 1, C5): This will compare if the cell is blank or not. If it returns True, then this will show the result 1.

→ OUTPUT: 1

  • IF(ISBLANK(C5), 1, C5)*B5*D5: This will multiply the cells containing the value.

→ OUTPUT: 1*550 = 550

Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)


Conclusion

The above methods will assist you to Multiply Cell Contains Value with Excel Formula. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo