To multiply only when a cell is not zero in Excel, use the IF function: =IF(A2=0,0,A2*B2) or, if you want a blank result, =IF(A2=0,"",A2*B2). This checks the condition first and only performs the multiplication when the value is not zero, avoiding errors or unwanted results from zero values.
Why You Might Need to Multiply Only Non-Zero Values
In real-world spreadsheets, zero often represents missing data, a placeholder, or a category that should not contribute to a calculation. For example, if you track inventory and a product has zero units sold, multiplying by a price would still give zero, which might be fine. But if you are calculating average order value or applying a discount rate, a zero in the wrong place can skew results or produce misleading figures. The IF function gives you control over how zeros are handled, letting you return a blank, a zero, or even a custom message.
How the IF Function Works for This Task
The IF function evaluates a logical test and returns one value if the test is true and another if false. The syntax is:
=IF(logical_test, value_if_true, value_if_false)
For our purpose, the logical test is A2=0 (or A2<>0 for not equal). If the test is true (the cell is zero), you decide what to return—often 0 or an empty string. If false (the cell is not zero), you perform the multiplication.
Example layout: Suppose column A contains quantities and column B contains unit prices. In cell C2, enter:
=IF(A2=0,0,A2*B2)
If A2 is 5 and B2 is 10, the result is 50. If A2 is 0, the result is 0, not 0*10 (which would also be 0, but the difference matters when you want to distinguish a real zero from a calculated zero).
Using IF with Not Equal To for Clarity
Some users prefer to write the condition as “not zero” for readability. The formula becomes:
=IF(A2<>0,A2*B2,0)
This reads: if A2 is not equal to zero, multiply; otherwise, return 0. Both versions are equivalent, but the second may be clearer when you are scanning a formula. Choose whichever you find easier to explain to others.
Returning a Blank Instead of Zero
Often you want an empty cell when the multiplier is zero, not a visible zero. Use an empty string as the false argument:
=IF(A2=0,"",A2*B2)
This leaves the cell blank, which is useful for charts or reports where zeros would clutter the view. Note that a blank cell is not the same as a zero in subsequent calculations—if you later sum that column, blanks are ignored, but zeros are counted. If you need to treat blanks as zeros, wrap the formula in N() or use IFERROR.
Handling Errors and Other Conditions
Sometimes you need to multiply only if the cell is not zero and not empty. An empty cell is treated as zero in arithmetic, but you might want to skip it. Use the AND function:
=IF(AND(A2<>0,A2<>""),A2*B2,"")
This returns a blank if A2 is empty or zero, and multiplies otherwise. You can extend this to check for text values or errors using ISNUMBER:
=IF(AND(ISNUMBER(A2),A2<>0),A2*B2,"")
This ensures A2 is a number and not zero before multiplying, which is robust for messy data.
Using SUMPRODUCT for Conditional Multiplication in Ranges
If you need to multiply and sum across a range, but only include rows where a condition is met, SUMPRODUCT is a powerful alternative. For example, to sum the product of A2:A10 and B2:B10 only when A2:A10 is not zero:
=SUMPRODUCT((A2:A10<>0)*(A2:A10*B2:B10))
Here, the condition (A2:A10<>0) returns an array of TRUE/FALSE, which Excel converts to 1/0 when multiplied. This multiplies each pair only if the condition is true, then sums the results. If you use Excel 365, you could also use FILTER and SUM, but SUMPRODUCT works in all versions.
Common Mistakes and How to Avoid Them
- Using
IF(A2=0,A2*B2,0)– This does the opposite: it multiplies when A2 is zero, which is rarely what you want. Double-check the logic. - Forgetting to lock cell references – When copying the formula down, relative references change. If you need a fixed cell, use absolute references like
$A$2. - Mixing text and numbers – If A2 contains text, the multiplication will return a
#VALUE!error. UseISNUMBERto guard against this. - Using
IFwith a range –IFonly works on a single cell unless you use array formulas. For ranges, useSUMPRODUCTorFILTER.
Alternatives: IFS and Boolean Multiplication
For multiple conditions, IFS can be cleaner. For example, to multiply if A2 is not zero and B2 is not blank:
=IFS(AND(A2<>0,B2<>""),A2*B2,TRUE,"")
Another trick is Boolean multiplication: =(A2<>0)*(A2*B2). This returns 0 when A2 is zero (because FALSE=0) and the product otherwise. However, it returns 0, not a blank, and can produce 0 even when A2 is a text value, so use it with caution.
Frequently Asked Questions
How do I multiply only if a cell is not zero and not blank?
Use =IF(AND(A2<>0,A2<>""),A2*B2,""). This checks both conditions and returns a blank if either fails. If you want to treat blanks as zeros, just use =IF(A2=0,0,A2*B2) because blank cells are treated as zero in arithmetic.
Can I use this with a whole column?
Yes, enter the formula in the first row and drag down. For a dynamic array that spills results, use =IF(A2:A10=0,0,A2:A10*B2:B10) in Excel 365, which automatically fills the range. In older versions, you would need to copy the formula down manually.
Why does my formula return #VALUE! when the cell contains text?
Because multiplication expects numbers. If a cell contains text, Excel cannot multiply it. Wrap the condition with ISNUMBER: =IF(AND(ISNUMBER(A2),A2<>0),A2*B2,""). This returns a blank instead of an error.
What is the difference between returning 0 and returning a blank?
Returning 0 keeps the cell numeric, which is useful for calculations like SUM or AVERAGE. Returning a blank (empty string) makes the cell look empty and is ignored by many functions, but it can cause issues if you later try to use the cell in arithmetic. Choose based on how the data will be used.
Does this work in Google Sheets?
Yes, the same formulas work in Google Sheets. The only difference is that in Google Sheets, you might use ARRAYFORMULA for ranges: =ARRAYFORMULA(IF(A2:A10=0,0,A2:A10*B2:B10)). In Excel, the dynamic array behavior is automatic in Microsoft 365.
Related
- Excel Conditional Formatting for Past Dates: How to Set It Up
- Excel Conditional Formatting for Past Dates
- Excel Conditional Formatting Duplicate Rows: Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Split Renal Function Test: What It MeasuresA split renal function test estimates how much each kidney contributes to the pair’s overall function. It is…
- How to Filter a Column in Excel by Dates or Date RangeTo filter dates in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel NPV With Different Rates: Formula, Steps, ExampleExcel’s NPV function accepts one discount rate, so it cannot directly calculate net present value when the rate…
- Excel Won’t Calculate Formulas? Causes, Checks, and FixesIf Excel does not calculate a formula, first check Formulas > Calculation Options and select Automatic. If that…
- Excel SUMIF with Dates: Rules, Examples, and FixesTo sum values for a date or date range in Excel, use SUMIF with a date criterion; for…
- CLEAN Function in Excel: Examples and Common FixesIf the text you want to clean is in A2, enter =CLEAN(A2) in another cell. For example, if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.