Sabrina Ayon
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.
How to Multiply Using Formula If Cell Contains Value in Excel: 3 Different Examples
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.
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.
- And, that’s it! Finally, we will be able to see in column E, that the cells only containing value will multiply.
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.
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.
- 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
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.
- 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
Download Practice Workbook
You can download the workbook and practice with them.
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
- How to Create a Multiplication Formula in Excel
- How to Multiply Multiple Cells in Excel
- How to Multiply Rows in Excel
- How to Multiply a Column in Excel by a Constant
- How to Multiply Two Columns and Then Sum in Excel
- How to Divide and Multiply in One Excel Formula
- How to Multiply by Percentage in Excel
- How to Do Matrix Multiplication in Excel
- How to Multiply from Different Sheets in Excel
