formula that returns a number based on a certain criteria

[Alexmedft]

I want a formula that returns volume only to the point revenue is greater than 0. All I want is to show me the total volume.


volume Revenue
400 $55.00
450 $48.00
500 $44.00
600 $39.00
650 $38.00
800 $20.00
1200 $17.00
1450 $5.00
1575 $2.00
1625 ($1.00)
1800 ($6.00)
2500 ($18.00)
2750 ($22.00)
3300 ($29.00)
4000 ($50.00)

[AliGW]

Try this:

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	volume	Revenue
2	400	55		7625
3	450	48
4	500	44
5	600	39
6	650	38
7	800	20
8	1200	17
9	1450	5
10	1575	2
11	1625	-1
12	1800	-6
13	2500	-18
14	2750	-22
15	3300	-29
16	4000	-50

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	D
2	=SUMIF(B2:B16,">0",A2:A16)

Sheet: Sheet1

[Alexmedft]

That works but I think I asked wrong. based on the value being greater than 0 it should return 1575 since that volume is increasing. can it just return up to the point the revenue is positive and not sum up the volume?

[AliGW]

Right! Try this:

=LOOKUP(2,1/(B2:B16>0),A2:A16)

[Alexmedft]

your the best that works thank you!