Formula/Summary Function

Carrie Clark

I want to be able to summarize how much our locations are requesting in funds, can someone help me with a formula?

Column A - Month

Column B - Date of Request

Column C - Amount

Column D - Agency (single select dropdown)

Column E - Use of funds (multi select dropdown)

Ideally I'd like to summarize column C, by column D - and if I can, by use of funds.

Example 1: Sample-Agency1 requested $x,xxx

Example 2: Sample Agency requested $x,xxx to use for Foster Care.

Smartsheet Sample.PNG

Find more posts tagged with

Accepted answers

All comments

Heather Duff

@Carrie Clark

Do you want to summarize them in a Sheet Summary or a separate sheet that has formulas that sum the data?

Carrie Clark

I would love to be able to do it in a separate sheet. It's my understanding using a separate sheet can also be better for dashboards.

Heather Duff

@Carrie Clark Definitely - I always create metric sheets to pull data from for my dashboards. It's the way to go, for sure.

I would create a sheet that has the following columns:

Agency

Use of Funds

Total

Then fill in the Agency and Use of Funds columns with the agency and use of funds categories, like this:

In the bolded (parent) rows, in the Total column, use this formula:

=SUM(CHILDREN())

In the other (children) rows, use this formula:

=sumifs({Amount range},{Agency Range}, [Agency]@row, {Use of funds range}, [use of funds]@row)

If your column that is for Use of Funds is a multi-select dropdown, then you may want to use this instead:

=sumifs({Amount range},{Agency Range}, [Agency]@row, {Use of Funds range}, CONTAINS([use of funds]@row, @cell))

Please note that the items with curly brackets are named cross-sheet ranges. Those names will be whatever you set them to be - or will default to the "(Name of your sheet) range 1" format.

Hope this helps. Let me know if it works!

Best,

Heather

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.