sum of cells but if zero then 3

[Fessy82]

Hi Guys

Been on all night with this one. I want to add 8 cells together but if the value of them are zero i want to make them a 3 in the formula.

Any ideas
fes

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE and AFTER results in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Write several different examples if needed, correct and false, to better understand problem.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pepe74287]

Hi,

assuming the cells are in range A1:A8, you can use this:

=SUM(A1:A8)+COUNTIF(A1:A8,0)*3

[Fessy82]

Hi there

Thanks for that Pepe it wasn't quite A1:A8 so used the following:
=SUM(D24+D47+D63+D29+D7+D36+D50+D11)+35+COUNTIF(D7,0)*3+COUNTIF(D36,0)*3+COUNTIF(D50,0)*3+COUNTIF(D11,0)*3
with help from yourself this has helped me out massively.

Only thing is the cell is showing up with #value as nothing has been input into the cells yet. the +35 is the starting value i want in the formula is there anyway to show this and have a running update i.e. if D7 is completed the cell will show 35+value of D7 and not #Value as the other cells are not complete?

Thanks in advice
fes

[Fessy82]

Alright Guys

Am getting on well with this now and getting better at excel as I say I only started using it a few days ago. few things am still to do (ive attached Excel spreadsheet for advice). sort the tables into points ascending i.e. most points at the top and lowest at the bottom when you complete the yellow cells. Also is there away to show the current points in the table instead of #value! if put their current points at the side and they are in the cell formula. any advice is much appreciated.

fes

[Fessy82]

this is the sheet

thanks in advance