Charnel has attended:
Excel Advanced course
Advanced formula's
If i have a number of columns in a sheet say a column for each month ie 12 columns.
What i'd like to know is how to sum only the columns which contain numbers in it for eg. column 1-10 have numbers in it and the last two columns have formulas but no numbers, how to i include a total column that automatically adds the columns with numbers and updated automatically if for instance a number is included in column 11?
Thanks
RE: Advanced formula's
Hi Charnel
Thanks for your question. To make sure I understand the problem can I check what happens when you put a sum function in column 13 that sums the values and the cells with formulas? Usually Excel's sum function will ignore the contents of cells with no value or non relevant text - if the contents of these cells changes to contain values that could be summed Excel automatically includes them in the result.
Let me know how you get on and we'll try to help further.
Kind regards,
Andrew
RE: Advanced formula's
Hi Andrew
So basically what i have is that in month 11 and 12 i have formula's which calculate a EURO amount based on the exchange rate input into another cell so in 11 and 12 it shows #DIV/0!.
Therefore in my total column it also shows #DIV/0!. And what i'd like it to do is add all columns as the #DIV/0! is cleared since the exchange rate in inputted and the amount is calculated.
Hope you can help
thanks
RE: Advanced formula's
Good Afternoon Charnel,
An option might be to fix the #DIV/0 using an If formula.
E.G if existing formula is =D14/D15 then new formula would be =IF(E15>0,E14/E15,). This checks if the dividing cell is greater than zero, if it is then it continues with the division, if not it returns no value.
Hope this assists
Regards
Tristan
