Return the value at the lowest end of the row

[Goddess]

Hi,

I have a row of data. However, I do not want to sum the whole row. I only want the result at the end of the row. In the examples below, if there are no data in Q2, Q3 & Q4, the total will return the value in Q1. However, if there are data in Q4, it will ignore the data above it (in Q1, Q2 & Q3). Any formula to do that?

Book1
	A	B	C	D	E	F	G	H
1
2	Example 1			Example 2			Example 2
3	Q1	500		Q1	500		Q1	500
4	Q2			Q2	200		Q2	200
5	Q3			Q3			Q3
6	Q4			Q4			Q4	400
7	Total	500		Total	200		Total	400
8
Sheet1

Thanks,
Irene

 

[LegendaryAxe]

Try this:

Book4
	A	B	C	D	E	F	G	H
1
2	Example 1			Example 2			Example 2
3	Q1	500		Q1	500		Q1	500
4	Q2			Q2	200		Q2	200
5	Q3			Q3			Q3
6	Q4			Q4			Q4	400
7	Total	500		Total	200		Total	400
Sheet1

Cell Formulas
Range	Formula
B7,H7,E7	B7	=LOOKUP(2,1/(B3:B6<>""),B3:B6)

 

[Goddess]

Thanks!

 

[AhoyNC]

Same idea, just a little short. The lookup value needs to be any number that is larger than what you would have in the column. In this case I used 150000.

Book1
	A	B	C	D	E	F	G	H
1
2	Example 1			Example 2			Example 2
3	Q1	500		Q1	500		Q1	500
4	Q2			Q2	200		Q2	200
5	Q3			Q3			Q3
6	Q4			Q4			Q4	400
7	Total	500		Total	200		Total	400
Sheet4

Cell Formulas
Range	Formula
B7,H7,E7	B7	=LOOKUP(150000,B3:B6)