Format SUM function in a report
Answers
-
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
-
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!
-
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
-
Thanks Genevieve, in the first instance I want the SUM of all excluding "Eee".
Can you please advise?
It didn't work for me
-
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")
-
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")
-
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?
-
I am receiving an error:
Yes, confirmed all the above.
I am doing it in the Summery sheet
Thanks
-
BTW - when I used the formula and did it manually i.e. choose the cells individually (using "Ctrl" key) it worked
-
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.
-
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!
-
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
-
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
-
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")
-
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!