Sum + Collect Formula - without knowing what the collect "criteria" might change to.
Hi,
I'm trying to collect the sum of income received from different sources. However, I don't know what the sources may be as new ones are added. Is there a formula/Solution that can look for all the different income sources and then sum the income amount:
ex:
SSA - $1,100
Pension A - $800
Pension A - $100
SSA - $1,000
I would want the formula to return:
SSA - 2,100
Pension A - 900
If I add Pension B to the first list, then the formula/solution should return Pension B and it's values...
Best Answer
-
Here's one way (example):
Column Total formula: =SUMIF(Type:Type, Type@row, Amount:Amount)
I hope this helps.
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers
-
Here's one way (example):
Column Total formula: =SUMIF(Type:Type, Type@row, Amount:Amount)
I hope this helps.
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Cool! thanks.
How would I extract a list so that I don't see duplicate of the payors:
(It can be as a report)
Pension A: 2,100
SSA: 900
Pension B: 500
-
Change the formula to =IF(COUNTIF(Type@row:Type$1, Type@row) = 1, SUMIF(Type:Type, Type@row, Amount:Amount))
Note: You can't make it a column formula any more due to direct cell reference "Type$1" so you need to make sure it's always copied down the column.
Then filter your report to only show rows where the Total is "Not Blank"
I hope that helps.
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!