# Multiple IF Then Function

I know this shouldn't be this difficult, but what I am looking to do is

I want a total in A5, based on a running total in A1 through A4.

For example, if A4 is blank then input A5=A3, if A3 is blank than A5=A2, if A2 is blank then A5=A1. If there was a value in A4 than A5=A4.

Thought: `IF(A4>"0",A4,A3),IF(A3>"0",A3,A2),IF(A2>"0",A2,A1),IF(A1>"0","A1",0))`

-
if -> then -> else -> if – Zac Bruce Jun 30 '11 at 21:53
From what you're saying, it's less of a total than a selection based on precedence of the source cells, with A4 having precedence over A3, A2 and A1; A3 having precedence over A2 and A1, and so on. At least that's my interpretation. If that's not right, what should happen when there are values in A1, A2, A3 and A4? – boot13 Jun 30 '11 at 22:09
How are you deriving the totals in the preceding cells? Couldn't subtotal(9,a1:a4) provide the same thing? In other words what will give you a zero in A2, A3 or A4? maybe there is another way to look at this – datatoo Jun 30 '11 at 23:22

What you do is this:

``````If (I want the value from A4) Then A4 Else (I don't want the value from A4, so...)
...If (I want the value from A3) Then A3 etc...
``````

If you write the statement on several rows it will be easier to see what happens.

``````IF(A4>0, A4,
IF(A3>0, A3,
IF(A2>0, A2, A1)
)
)
``````

Put it all together in one row, and you get

``````IF(A4>0,A4,IF(A3>0,A3,IF(A2>0,A2,A1)))
``````

which should do what you want it to.

-
IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) – Zac Bruce Jun 30 '11 at 22:05
@Zac, isn't that exactly what I've done? From the last line in the question I inferred that the OP knows the basic syntax of the `IF` statement. – Tomas Lycken Jul 1 '11 at 6:43
I found your description of "If (I want the value from A4) Then A4 Else (I don't want the value from A4, so...) ...If (I want the value from A3) Then A3 etc..." harder to understand. – Zac Bruce Jul 1 '11 at 22:56

From what I understand, you're trying to get the value of the last non-blank cell in a range of cells (in your example, range A1:A4). If so, this formula might help you.

``````=OFFSET(\$A\$1,COUNTA(\$A\$1:\$A\$4)-1,0)
``````

Enter it into A5 (based on your example, that is). For a larger range of cells, just adjust the references.

The formula will return an #REF! when all cells are blank.

-