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)),"",A2B2)

3


1

1

=IF(AND(ISBLANK(A3),ISBLANK(B3)),"",A3B3)

4




=IF(AND(ISBLANK(A4),ISBLANK(B4)),"",A4B4)

5

4

1

3

=IF(AND(ISBLANK(A5),ISBLANK(B5)),"",A5B5)

6




=IF(AND(ISBLANK(A6),ISBLANK(B6)),"",A6B6)

7

6

1

5

=IF(AND(ISBLANK(A7),ISBLANK(B7)),"",A7B7)

8

7


7

=IF(AND(ISBLANK(A8),ISBLANK(B8)),"",A8B8)

9

8

1

7

=IF(AND(ISBLANK(A9),ISBLANK(B9)),"",A9B9)

No comments:
Post a Comment