Survey results (and what's coming next)

Thank you all for giving us your feedback in the recent Smartsheet Community survey! Based on your thoughts, we're excited to announce that we have many changes coming in just a few weeks. Read our survey recap post for more details.

## 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:=SUMIF

S([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 sheetas 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 formulasLet me know if I can help further!

Cheers,

Genevieve

Thanks Genevieve, in the first instance I want the SUM of all

"Eee".excludingCan 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")

@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")

@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?

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

spacebetween 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 Institutio

n:InstitutionSee: 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 SUM

IFSwith 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!