While performing the calculation to ignore the blank cells we will use ISNUMBER, AND and IF formulas in Microsoft Excel.
ISNUMBER: This function can be used to check if a cell contains a number.
IF: -IF condition allows us to use multiple conditions in a cell. It helps us to make the function flexible and Excel allows upto a max of 64 conditions which can be checked in a cell.
Example:Cells A2 and A3 contain the numbers 3 and 5. If the cell contains 3, then the formula should display “Yes” otherwise “No”.
AND: This function is an operator function and is used to combine multiple criteria for a cell
Example:Column A contains 3 numbers. We need to check if these numbers are greater than 10 and less than or equal to 50.
2 of the cells have numbers which are not greater than 10 AND less than or equal to 50. Hence they show as False, while the last number has a True result.
Let’s take an example and understand how you can ignore blank cells when performing calculations.
We have 2 lists in columns A & B, in which some cells are containing numbers and some cells are blank. In this example we need to multiply the numbers from the corresponding cells in both the columns. Our condition is that if any cell among the two is blank, the formula should give a blank result, else it should multiply the numbers in both the cells.
We find that once the formula has been copied to the whole column, only cells C2 and C5 show the result, while C3, C4 and C6 are blank as any of the cells in these rows are blank.
In this way you can use this formula to ignore the blank cells while the calculation is performed.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.