SOLVED

Formula to add 100 for every 0

Copper Contributor

In cells F12-J12 if any cells are 0 need 100 for each 0 to be added to L12. Any help would be greatly appreciated.

 

 

5 Replies
best response confirmed by CassandraMar (Copper Contributor)
Solution

@CassandraMar 

=COUNTIF(F12:J12,0)*100

Is this what you are looking for? 

Thank you! That worked, do you know how to combine the two formulas below?

=L12-K12
=COUNTIF(F12:J12,0)*100

@CassandraMar 

=COUNTIF(F12:J12,0)*100-K12

Maybe with this formula if i correctly understand what you want to do.

@OliverScheurich 

 

I moved the formula over to M12, I need =COUNTIF(F12:J12,0)*100 and =L12-K12 to be in the same cell.

In this situation L12 is our beginning balance, K12 is our total paid and M12 is our ending balance. I need our total paid (K12) to be subtracted from the beginning balance (L12) and combined with the formula you gave me above in (M12) . For every unpaid week we will put a 0 which adds the 100 to the ending balance in M12 calculating the total unpaid balance including the 100 added for each 0 as well as total partial payments or full payments inputed in F12:J12. I have a formula in K12 giving the sum of F12:J12.

 

For example see the screenshot I have included. 

Screen Shot 2022-03-01 at 10.46.13 AM.png

@CassandraMar 

=L12+COUNTIF(F12:J12,0)*100-K12

You can try this formula in cell M12 which seems to work in my spreadsheet.

1 best response

Accepted Solutions
best response confirmed by CassandraMar (Copper Contributor)
Solution

@CassandraMar 

=COUNTIF(F12:J12,0)*100

Is this what you are looking for? 

View solution in original post