### Calculating only when two cells are not blank

Earlier this week I received an email from a reader asking how to add / subtract cells only when there was no blank data between the rows.

I.E…. He only wanted to add / subtract cells in Column A and B when there were values in either cell or just one cell. If both cells did not have a value, then he didn't want to return a value.

Well this can be accomplished by combining an If statement with a test for blank value.
In this example, the formula sums in Column C for Rows 2 and 3 since there is a value in either Column A or B. For row 4, the formula does not return a value since Columns A and B are blank.

The formula checks for a blank value in the cell in Column A and for a blank value in the cell in Column B. If both are blank, the formula returns nothing “”, else it subtracts the cell in Column A from the cell in Column B.

 A B C D 1 column 1 column 2 A - B = Answer Formula 2 1 1 0 =IF(AND(ISBLANK(A2),ISBLANK(B2)),"",A2-B2) 3 1 -1 =IF(AND(ISBLANK(A3),ISBLANK(B3)),"",A3-B3) 4 =IF(AND(ISBLANK(A4),ISBLANK(B4)),"",A4-B4) 5 4 1 3 =IF(AND(ISBLANK(A5),ISBLANK(B5)),"",A5-B5) 6 =IF(AND(ISBLANK(A6),ISBLANK(B6)),"",A6-B6) 7 6 1 5 =IF(AND(ISBLANK(A7),ISBLANK(B7)),"",A7-B7) 8 7 7 =IF(AND(ISBLANK(A8),ISBLANK(B8)),"",A8-B8) 9 8 1 7 =IF(AND(ISBLANK(A9),ISBLANK(B9)),"",A9-B9)

#### 1 comment:

1. I want to add cells D4 and F4. IF Both cells are blank G4 = Blank. if any of the cells D4 and F4 carries any value say D4=40, then G4=40.I tried with the formula G4 =IF(AND(ISBLANK(D4),ISBLANK(F4)),"",D4+F4). It is not working. Kindly suggest