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)

No comments:

Post a Comment