Running total, maybe SUMIFS??

[krispatterson]

Hi all

data as follows:

	A	B	C	D
1	10	5	N
2	5	1	N
3	6	12	Y
4	1	3	N
5	14	13	Y

<tbody>
</tbody>

I need a formula in D1 that:

> Looks at Column C
- if "N", puts the figure from Column A as a - (negative).
- if "Y", puts the figure from Column B as a + (positive).

I then want it to run a cumulative running total, so that the results in Column D are:

	D
1	-10
2	-15
3	-3
4	-4
5	10

<tbody>
</tbody>

There will also be instances where Column C is blank - I need the formula to ignore those instances.



Hope that makes sense - I think I might need a SUMIFs, but can't figure out how to get it working!!

Thanks in advance

 

[SyedUsman]

=if(c1="",offset(d1,-1,0),if(c1="n",a1*-1,b1)+iferror(offset(d1,-1,0),0))

 

[JayBurn]

Least complex way would be to add a helper column with =IF(D2="y",+C2,IF(D2="n",-B2,"")), then you can use =SUM($E$2:E2) in the next column and copy them down.

 

[steve the fish]

I think your last 10 should be 9. You could try these two formulas. In D1:

=IF(C1="N",-A1,IF(C1="Y",B1,""))

In D2:

=IF(C2="N",-A2,IF(C2="Y",B2))+IF(D1<>"",D1)

 

[krispatterson]

thanks to all - steve the fish, I used your formula and it works perfectly! & yes, you're right - my maths clearly leaves a bit to be desired