There are many ways to force excel for calculating a formula only if given cell/s are not blank. In this article, we will explore all the methods of calculating only "if not blank" condition.
So to demonstrate all the cases, I have prepared below data
In row 4, I want the difference of months of years 2109 and 2018. For that, I will subtract 2018’s month’s data from 2019’s month’s data. The condition is, if either cell is blank, there should be no calculation. Let’s explore in how many ways you can force excel, if cell is not blank.
Calculate If Not Blank using IF function with OR Function.
The first function we think of is IF function, when it comes to conditional output. In this example, we will use IF and OR function together.
So if you want to calculate if all cells are non blank then use below formula.
Write this formula in cell B4 and fill right (CTRL+R).
How Does It work?
The OR function checks if B3 and B2 are blank or not. If either of the cell is blank, it returns TRUE. Now, for True, IF is printing “” (nothing/blank) and for False, it is printing the calculation.
The same thing can be done using IF with AND function, we just need to switch places of True Output and False Output.
In this case, if any of the cells is blank, AND function returns false. And then you know how IF treats the false output.
Using the ISBLANK function
In the above formula, we are using cell=”” to check if the cell is blank or not. Well, the same thing can be done using the ISBLANK function.
It does the same “if not blank then calculate” thing as above. It’s just uses a formula to check if cell is blank or not.
Calculate If Cell is Not Blank Using COUNTBLANK
In above example, we had only two cells to check. But what if we want a long-range to sum, and if the range has any blank cell, it should not perform calculation. In this case we can use COUNTBLANK function.
Here, count blank returns the count blank cells in range(B2:H2). In excel, any value grater then 0 is treated as TRUE. So, if ISBLANK function finds a any blank cell, it returns a positive value. IF gets its check value as TRUE. According to the above formula, if prints nothing, if there is at least one blank cell in the range. Otherwise, the SUM function is executed.
Using the COUNTA function
If you know, how many nonblank cells there should be to perform an operation, then COUNTA function can also be used.
For example, in range B2:H2, I want to do SUM if none of the cells are blank.
So there is 7 cell in B2:H2. To do calculation only if no cells are blank, I will write below formula.
As we know, COUNTA function returns a number of nonblank cells in the given range. Here we check the non-blank cell are 7. If they are no blank cells, the calculation takes place otherwise not.
This function works with all kinds of values. But if you want to do operation only if the cells have numeric values only, then use COUNT Function instead.
So, as you can see that there are many ways to achieve this. There’s never only one path. Choose the path that fits for you. If you have any queries regarding this article, feel free to ask in the comments section below.
Related Articles:
How to Calculate Only If Cell is Not Blank in Excel
Adjusting a Formula to Return a Blank
Checking Whether Cells in a Range are Blank, and Counting the Blank Cells
Only Return Results from Non-Blank Cells
Popular Articles
50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
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.
HI,
I am trying to work it out a nested formula that sums a different range of cells based on the quarter AND if the cells are not blank.
I would like the total to be calculated as follow in one formula:
If A3=Qtr 1 AND Jan to Mar is blank, return blank, otherwise sum Jan to Dec.
If A3=Qtr 2 AND Apr to Jun is blank, return blank, otherwise sum April to Dec.
If A3 = Qtr 3 AND Jul to Sept is blank, return blank, otherwise sum Jul to Dec
If A3 = Qtr 4 AND Oct to Dec is blank, return blank, otherwise sum Oct to Dec
Is it possible to nest these together or is there another way to build the formula? I have tried various IFs, ISBLANK, SUMIFs, etc but cant not get a valid result.
Thanks for your help.
I would like to use the COUNTA formula for a range. I need the formula to check each cell in the range A4:T4, if there are any blank cells do nothing, if there are NO blank cells... I want the formula to highlight that range with Orange 2, 40%. How can I make this happen? Can you help?
I believe your formula in the introduction about ISBLANK, above in this page, has an error: You show (ISBLANK(B3),ISBLANK(B3)),...
Shouldn't one of the B3 be B2?
Here is what you wrote:
Using ISBLANK function
In the above formula, we are using cell=”” to check if cell is blank or not. Well same thing can be done using ISBLANK function.
=IF(OR(ISBLANK(B3),ISBLANK(B3)),””,B2-B3)
Thanks David. We've corrected the content now.