# Ignoring Blank Cells when Performing Calculations

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.

For Example:-

• Cell A2 contains the number 123
•  =ISNUMBER (A2), function will return “TRUE”
• Cell A3 contains the text “XYZ”
• =ISNUMBER (A3), function will return “FALSE”

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”.

=IF (A1=3,"Yes","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.

=AND(A1>10,A1<=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.

• Select the cell C2.
• Enter the formula =IF(AND(ISNUMBER(A2), ISNUMBER(B2)),A2*B2," ")
• Press enter on the keyboard.
• The function will return 3 in cell C2, as both the cells contain numbers.
• Copy the formula by pressing the key “CTRL+C” and paste it in the range C3:C6 by pressing the key “CTRL+V” on the keyboard.

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.

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 info@exceltip.com

## Users are saying about us...

1. I have a formula to calculate time as follows, where C9 is 8:00am and D9 is 6:00PM:

=IF(D9>C9,D9-C9,1-(C9-D9))

This returns a value of 10.

I want to edit the formula to return a blank cell if C9 and D9 were empty.

Help me!!??

• Hi Jenna,
if you want to return a blank cell if any of C9 and D9 is empty then write this formula.

=IF(OR(D9="",C9="")," ",IF(D9>C9,D9-C9,1-(C9-D9)))

if you want to return a blank cell if both C9 and D9 are empty then write this formula.

=IF(AND(D9="",C9="")," ",IF(D9>C9,D9-C9,1-(C9-D9)))

2. My current formula is to compare goals scored in a football game. =IF(M3=P3,1,0) is the formula I'm using to calculate a draw, how do I change this to produce a blank result if either/both M3 & P3 are blank?

3. Thanks, need to put an ABS formula in here as well to work out difference between L2-K2 but with no negative values but i cannot get it to work. Any idea?

=IF(AND(ISNUMBER(I2), ISNUMBER(K2)),I2-K2,” “)
=IF(AND(ISNUMBER(I2), ISNUMBER(K2)),=ABS(I2-K2,” “))

Thanks, John

4. Alfonso L Vargas Roman

Thanks for this tip,

I tried dozens of formulas unsuccessfully but your's work. I was adding contiguous cells on each of hundred rows to then obtain an average of the sums. Some formulas returned unwanted zeros or blanks. I just wanted zeros if the sum really returned zeros or blanks if the rows were empty.

However, it caused an unwanted situation. I have an Index to identify the highest value of the sums highlighting the corresponding range row. Now I have alternated highlighted rows in the blank or empty rows (all the rows have alternated fill as a visual aid). How can I fix this thing?

5. Hi! How can i make this formula to show me the result if one or more of the cells in the formula are blank?
I have the following formula in my excel and i don't know exactly how to make it show me the result:
=IF(AND(ISNUMBER('31'!I8),ISNUMBER('30'!I8),'31'!I8+'30'!I8)
it shows me only true or false because one of the cells are blank

6. I am trying to formulate averages of multiple cells, but in some cells in the row there is no value. I keep getting the #DIV/0! Error. How do I make it give the average while ignoring the blank cells? Thanks!!

7. I am trying to get a cell to add one day to the date in A5 (=A5+1), but return nothing if A5 is blank. Anyone know how?

8. Hello!
I am trying to add rules to the formula where if the first field is blank, then it won't do the calculation. But if it is not blank, then to complete the calculation.

The calculation I am using is: =-G10*F11+G10
So if F11 is blank, do not provide a result
If F11 has a figure, complete the result.

Thank you

9. I am using the left function to return anything that is before "-" some of my cells in the column I am referencing are blank and I want it to just return a blank and not #Value
Here is my formula =LEFT(O2,SEARCH("-",O2)-1)

10. Hi
I'm running the following array formula to track MAX, MIN and AVERAGE across a range of cells but cannot get it to ignore zeroes/blanks. Any suggestions on how I can amend?

{=MAX(B\$24,MAX(IF(MOD(COLUMN(G\$24:DH\$24)-COLUMN(G\$24)+0,5)=0,G\$24:DH\$24)))}

Thanks
Rod

• Hello, i'm trying to get my formula to display nothing when cell J188 is blank however is doesn't work.
=WORKDAY(J188,K188,Holidays!\$A\$2:\$A\$49)

11. Hi,
I am trying to write a formula which identifies if data complies with a set of values but I want the calculation cell to be blank if there is no data in the cell.

So far I have =IF(AND(I13>=0.5,I14<=2),"Y","N"

What will I need to add for the calculating cell to be left blank if the data cell is also left blank?