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

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