Formula/Summary Function

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.


Answers

  • Heather Duff
    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?

  • 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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!