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)
|
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
ReplyDelete