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
-
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.
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!