Help! Complete newbie trying to operate a basic expensive spreadsheet after 25 years of hand written

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Margaretann Bennett

    The reason you're seeing an error is because the column name isn't quite the same - see how your name at the top has a space between "Expense" and "1"?

    You'll need to have the Space in your formula as well (names will need to be identical, down to each character).

    =SUM([Expense 1]:[Expense 1])


    Then once you have this in one column, you can simply drag-fill the formula across your row to the right to have it update the referenced column automatically!

Answers

  • Yes! I am so useless I cannot even work out how to ask a question properly in this forum🤭

    As I was trying to ask above... help! I'm old skool, have been doing paper hand written spreadsheets for 25 years!

    Have managed to create an expenses spreadsheet on here with named column where I vertically add my expenses.

    Please can someone tell me the simple process for adding these columns up vertically to get the total in each column? I have contacted customer support email twice, who took ages to respond and then simply redirected me to the useless worksheets, which are not responding for me as directed.

    I click within a cell at the bottom of a row of numbers, I go to the top right in the tool bar and select SUM. This opens a window which I don't understand and cannot navigate. I get error message in the box when I enter return.

    I am missing something no doubt. Please there must be a simpler process to do such a basic function as adding up some numbers?

  • James Keuning
    James Keuning ✭✭✭✭✭

    Put this in that cell:

    =SUM([YourColumnName]:[YourColumnName])

    But hitting that SUM button and then just hitting the ENTER key should get you what you need.

  • I get the same error message #INCORRECT ARGUMENT SET appearing in the cell

    Is there no simple setting I can click in the tool bar to add a column of numbers without having to type in an elaborate equation? I can add that information to the cell using the SUM tool but when I hit return the error message always appears in the cell.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Margaretann Bennett

    Can you post a screen capture of your sheet with the formula open? It would be useful to know exactly how you're adding this formula, but please block out sensitive data.

    If you just click the "SUM" function from the top bar, you'll still need to add in what column you want to SUM within (these) otherwise you'll see an error.

    Here's an example:

    This article goes through how to reference columns: Create a Cell or Column Reference in a Formula

    Note that you can only reference the column in a formula once if you're putting that formula IN the same column that you're evaluating.

    Another way to quickly get a SUM would actually be to create a Report from your sheet. (See: Build a row report). You can choose what columns to include in the Report, then use the Summarize function at the top menu bar to SUM a specific column. See: Summarize content to extract key information with report builder

    Cheers,

    Genevieve

  • Thanks @Genevieve P. I have added grabs of a screen recording. I cannot make my formula look as I see it on your illustration. Instead I have a window which opens out which I don't understand.

    At 0.27 I click return, this is where I get the error message. I delete and try again by right clicking the cell instead... this is where I cannot access any further option.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Margaretann Bennett

    Thank you for these screen captures! This definitely helps. It does look like you are adding

    =SUM(

    without including the column reference at all. If you click enter after adding =SUM(, the formula doesn't know which column you're looking to SUM.

    You can see that the helper window is highlighting what you need to do next in the formula:


    So we need to add in the numbers to SUM.

    In your instance, it's the column that you're currently typing into! This means you can ether click on the Top Cell in the column, then add a : , then click on the bottom cell. Or you can manually type in the column name twice with : between it, like so:

    =SUM([Expense 1]:[Expense 1])


    This tells the formula to SUM the entire [Expense 1] column.

    In your Expense 2 column, you'll need to replace the column name:

    =SUM([Expense 2]:[Expense 2])

    Does that make sense?

    Cheers,

    Genevieve

  • Thanks @Genevieve P.

    I have attached screen shots.

    The first suggestion of adding a : has not worked

    I have typed in exactly what you suggested writing out column names. When I click return I get this error message #UNPARSEABLE

    ???

    I thought the tool sum function was supposed to provide a shortcut to having to write a formula in every column every time you want to add up, which seems very clunky and round about?


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Margaretann Bennett

    The reason you're seeing an error is because the column name isn't quite the same - see how your name at the top has a space between "Expense" and "1"?

    You'll need to have the Space in your formula as well (names will need to be identical, down to each character).

    =SUM([Expense 1]:[Expense 1])


    Then once you have this in one column, you can simply drag-fill the formula across your row to the right to have it update the referenced column automatically!

  • Thanks @Genevieve P.

    I see my mistake and have now corrected and been able to make it work😅I have fiddled around and got your first suggestion to work also.

    This is an awesome learning curve for me!

    As I was playing around... I discovered this also: If I click the cell directly beneath the numbers in the column, then hit the SUM tool, it pastes the formula in that cell for me automatically.

    Also if I drag/select the numbered cells in the column before hitting return on the formula, this works too. Yay! And this is an easier way really?

    Thankyou for your help and patience. My feedback on the actual linked worksheets is that they have not been intuitive and ultimately not helpful. Your illustration with the green arrows etc. much better!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Margaretann Bennett

    I'm glad to hear this now works for you! 🙂

    I agree that images can be very helpful - you may want to watch some of the Onboarding Webinars to get you started: SmartStart Onboarding

    There's also a few webinars specific to formulas: Formulas webinar series

    Cheers,

    Genevieve