Format SUM function in a report

2

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    I'm glad this will work for now!

    Can you identify what it is you're looking to filter out? We can use a SUMIF function if you have one criteria (example, filter out a Status of "In Progress" or something):

    =SUMIF([Status Column]:[Status Column], "In Progress", [Sum Column Name]:[Sum Column Name])


    There's also a plural SUMIFS function so you can filter by more than one column, but the structure of it is opposite to the one above, with the Sum Column at the front, like so:

    =SUMIFS([Sum Column Name]:[Sum Column Name], [Status Column]:[Status Column], "In Progress", [Name Column]:[Name Column], "Genevieve")

    Does that help?

    Cheers,

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    Hi Genevieve P.,

    I have a table that has names and dollar amounts. I would a formula that will give a SUM of a few cells and exclude a few.

    For example a total SUM of the cells excluding the highlighted cells

    So basically I'd like the total of all but the ones associated with the name “Eee”.

    Also a formula that will exclude a few “Names” for example: exclude “Eee” and “Kkk”

    Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    No problem! You can do this with a SUMIFS.

    With a SUMIFS (plural), first you list the column you want to SUM. Then you list the column that has criteria, or things you want to include/exclude, and then you list the criteria itself

    If your first instance, were you only want "eee", you would do that like so:

    =SUMIFS(Balance:Balance, Name:Name, "Eee")

    Does that make sense? The second half of the formula acts like the filter for the rows.

    Then when you want to exclude specific names, we will do that by using the AND function to identify all of the ones you don't want, and use @cell <> to say "the cell is not", like so:

    =SUMIFS(Balance:Balance, Name:Name, AND(@cell <> "Eee", @cell <> "Kkk"))


    Now, this is presuming your formulas are in the same sheet as the columns, such as in a Sheet Summary field. You can do the same calculations cross-sheet by replacing the [Column References] in these with Cross Sheet References {like this}. See: Cross-sheet formulas

    Let me know if I can help further!

    Cheers,

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    Thanks Genevieve, in the first instance I want the SUM of all excluding "Eee".

    Can you please advise?

    It didn't work for me

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    My apologies for reading that wrong! No problem, you just need to add the <> in front of the value you want to exclude:

    =SUMIFS(Balance:Balance, Name:Name, <> "Eee")

  • TaliRXM
    TaliRXM ✭✭✭

    @Genevieve P. ,

    Thanks. It doesn't work. Do I need to add row #'s, meaning to the formula you suggested:

    =SUMIFS(Balance:Balance, Name:Name, <> "Eee")

    =SUMIFS(Balance1:Balance50, Name:Name, <> "Eee")

  • Genevieve P.
    Genevieve P. Employee Admin

    @TaliRXM

    When you say it doesn't work, are you receiving an error or an incorrect result?

    No, you shouldn't put row numbers in as you want it to evaluate the entire column, so as new rows are added the formula will update. Have you confirmed that the column names are the same as what's in your Smartsheet sheet?

  • TaliRXM
    TaliRXM ✭✭✭

    I am receiving an error:

    Yes, confirmed all the above.

    I am doing it in the Summery sheet


    Thanks

  • TaliRXM
    TaliRXM ✭✭✭

    BTW - when I used the formula and did it manually i.e. choose the cells individually (using "Ctrl" key) it worked

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    Can you post a screen capture of the formula open? Unparseable usually means that there's a comma out of place or that the column name isn't spelled correctly.

  • TaliRXM
    TaliRXM ✭✭✭

    Hi,

    Entered the formula again and still gives an error message

    Here is the formula entered. Names and all information checked and very accurate:


    Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @TaliRXM

    Thank you for this screen capture! The reason why clicking on the cell brings in the right information whereas typing it doesn't work is because your column name has a space between letters. Any column names that have spaces or numbers need to be written within [these] so the formula knows when the name starts and stops.

    Try this:

    =SUMIFS([Payment status]:[Payment status], Institution:Institution, <> "Stanford")

    Also note that there is no space between Institution:Institution

    See: Create a Cell or Column Reference in a Formula

    Cheers!

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    Hi Genevieve P. 

    Thanks again.

    Your last suggestion worked, but gave the total SUM (following the formula)

    =SUM([Payment status]:[Payment status], "Criteria", [Institution]:[Institution]) 

    What formula do I use to exclude one "Name"? No matter what I put in the "Criteria" field and even if avoiding it at all - it still gives the total SUM

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    You're missing the "not" part of the formula. It's the symbols <> , essentially the opposite of =

    <> "Stanford"

    means "not Stanford"

    Does that make sense?

    Try it again, but with <>. Make sure you're also using SUMIFS with the IFS at the end, too, since you have criteria.

    =SUMIFS([Payment status]:[Payment status], Institution:Institution, <> "Stanford")

  • TaliRXM
    TaliRXM ✭✭✭

    It worked!!!

    What through me off was that in the previous formula (SUM formula), I thought the "Criteria" was meant for the scenario when want to exclude one...

    Thanks much Genevieve for your suggestions and patience!