Multiplying Cells With Values in Excel: 3 Examples

The following dataset contains sales across a small company for the first 5 days of each month; however, not every day of every month recorded sales. Suppose we want to multiply those sales that were recorded to determine total revenue so that our company can see how well it’s doing, given that number of sales is an important indicator of its health: for example, determining the effectiveness of marketing initiatives, assessing efforts made by sales personnel, and identifying ideal locations for new stores.

Example 1. Apply the PRODUCT Function to All Cells With Values

The PRODUCT function multiples large amounts of information quickly, in this case all values provided as arguments. It is particularly handy when dealing with multiple cells Its mathematical operator (*) can also be used to execute the same action, except it cannot handle empty cells (i.e. without value) and give an error. In other words, this function multiplies all cells that contain value and automatically ignores any cell which does not. .

Let’s return to our example, to which we have added a fourth column. The dataset indicates sales in January in column B, sales in February in column C and sales in March in column D. The PRODUCT function goes in column E.

STEPS:

Select E5 and enter the following formula:

=PRODUCT(B5,C5,D5)

Press Enter.

Note that only those cells with values have been multiplied, i.e. as only B5 and D5 contain values and C5 does not, only B4 and D5 have been multiplied and that the result (as well as the formula in the formula bar) appears in E5.

To duplicate the formula, click and drag the Fill Handle down the targeted range OR double-click the Plus (+) sign to AutoFill.

We now know the total number of sales between January and March.

Example 2. Apply the IF Function to Cells Containing a Value

The IF Function is among Excel’s most commonly used, allowing you to create logical comparisons between a given number and a choice of two conditions: True or False.

As before, our example contains three columns of data and a fourth for results.

STEPS:

Select E5 and enter the following formula:

=IF(B5="",1,B5) * IF(C5="",1,C5) * IF(D5="",1,D5)*IF(B5&C5&D5="",0,1)

Press Enter.

Note that only those cells with values have been multiplied, i.e. since only B5 and D5 contain values and C5 does not, only B4 and D5 have been multiplied; and that the result (as well as the formula in the formula bar) appear in E5.

To duplicate the formula, click and drag the Fill Handle down the targeted range OR double-click the Plus (+) sign to AutoFill.

How Does the Formula Work?

IF(B5=””,1,B5) * IF(C5=””,1,C5) * IF(D5=””,1,D5) determines whether a given cell is empty; if so, the result is False (where False is represented by the double quotation marks)

→ OUTPUT: 22*1*25 = 550

IF(B5&C5&D5=””,0,1) determines whether a given cell contains a number; if so, the result is True (where True is represented by 1).

→ OUTPUT: 1

Together, IF(B5=””,1,B5) * IF(C5=””,1,C5) * IF(D5=””,1,D5)*IF(B5&C5&D5=””,0,1) reveals the total number of sales over the three-month period.

→ OUTPUT: 550*1 =550

Example 3. Combine IF and ISBLANK Functions to Cells Containing a Value

ISBLANK is used to determine whether or not a cell is empty: if so, the result is TRUE; if not, the result is FALSE. As the dataset indicates there were no sales in February, combining IF and ISBLANK removes those cells from the calculation and multiplies those that remain.

Here too, our example contains three columns of data and a fourth for results.

STEPS:

Select E5 and enter the following formula:

=IF(ISBLANK(C5), 1, C5)*B5*D5

Press Enter.

Note that only those cells with values have been multiplied, i.e. as only B5 and D5 contain values and C5 does not, only B4 and D5 have been multiplied; the result appears in E5.