Limit a running total, then show 0.00

[DashboardNovice]

In the attached file, I have a running total in column H, which is capped at $20,000.

However, after it hits $20,000 for the first time, I would like to show $0.00 afterwards (in the yellow cells).

I just can't figure out how to set all instances of $20,000 (after the first occurrence) to $0.00.

I feel the solution is probably quite easy, but I have been working on this for a few hours. Any suggestions?

 

[Dave2018]

in H8:

=IF(H7=20000,G8,SUM(G8,H7))

drag down using the fill handle

 

[Ajesh]

Simple correction in your formula:

=IF(SUM($G$8:G8)>20000,0,SUM($G$8:G8))

further, you have Accounting Format applied to your cells due to which you will see $ - for zero values, so if you want to see $0.00 in cells, apply custom format as $#,##0.00;-$#,##0.00.

BR/Ajesh

 

[pecoflyer]

Just for the fun =MIN(SUM($G$8:$G8),20000)*(SUM($G$8:$G8)<=20000)

 

[GraH - Guido]

While we are at it
Pecoflyer's solution simplified =SUM($G$8:$G8)*(SUM($G$8:$G8)<=20000)
or =SUBTOTAL(9,$G$8:G8)*(SUBTOTAL(9,$G$8:G8)<=20000)

 

[bosco_yip]

Here is a table for different formulas comparison, I changed the G27 value from 1000.00 to 1001.00 and the results in H27:L27 show some different.

My formula solution no. 5 is

1] In L8, enter formula :

=MIN(20000,N(L7)+G8)

And,

select L8 >> Conditional formatting >> new rule >> use a formula….>>

>> rule of formula : =COUNTIF($L$8:$L8,$L8)>1

>> Format >> Custom cell format, enter: _($* \0.\0\0_)

>> OK >> OK

Then,

all copied down

2] See attached file

3] The OP should choice the formula in meet with his own requirement

Regards
Bosco