Rollup summary of data
This is probably a very simple formula required but I am struggling with it. Can somebody please assist?
We have a number of clients on one sheet where we track different trades budget vs. actual for each client. e.g. Plumbing, Electrical, Carpentry etc.
- We want to count up all the budgets for the Plumbing for all clients
- We want to count up all the actuals for the Plumbiing for all clients
- .....and the same for all the other trades
Attached is a snippet of the current layout. Each client is formatted the same; one under the other.
Lastly I want to display all these metrics on a dashboard :)
Thank you in advance.
Best Answers
-
You are probably going to want to create another sheet to summarise these
The other sheet should have
1) column for Trades, where you will put the name of the trade on each row, such as Plumbing
2) column for sum of all Budget amounts
3) column for sum of all Actuals amounts
Now use the command
SUMIFS(range, criterion_range1, criterion1 [,criterion_range2,criterion2...])
- Create a row in column Trades for each trade, Plumbing etc
- for each row use the SUMIFS formula and reference the other sheet (lets call it Transactions) in each column
- So for the Budget column, the formula will be
=SUMIFS({Transactions Budget},{Transaction Project},Trades@row)
{Transactions Budget} will reference the entire [Budget $] column of the Transactions sheet
{Transaction Project} will reference the entire [Project] column of the Transactions sheet
Do the same thing for the [Actuals $] column
Use the Reference Another Sheet link when entering the SUMIFS formula, it should appear when you are trying to enter the range.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Hi,
Depending on how many metrics you need you could use the sheet summary, but if there are many and you want to calculate further on them you'd need to add them in the sheet somewhere or on a so-called Master Metrics Sheet.
Try something like this. Replace the BUDGET $ to actual for that formula.
=SUMIF(PROJECT:PROJECT; "Plumbing"; [BUDGET $]:[BUDGET $])
The same version but with the below changes for your and others convenience.
=SUMIF(PROJECT:PROJECT, "Plumbing", [BUDGET $]:[BUDGET $])
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Did it work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Frank and Andree,
Your advice is helpful and I am just trying to get it right.
Kind regards
-
Happy to help!
Glad we got it working!
✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå I thought of that but realised there would need to be too many.
I did not want to include in the answer as it may have been confusing.
Good to have it here though for others to reference.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Answers
-
You are probably going to want to create another sheet to summarise these
The other sheet should have
1) column for Trades, where you will put the name of the trade on each row, such as Plumbing
2) column for sum of all Budget amounts
3) column for sum of all Actuals amounts
Now use the command
SUMIFS(range, criterion_range1, criterion1 [,criterion_range2,criterion2...])
- Create a row in column Trades for each trade, Plumbing etc
- for each row use the SUMIFS formula and reference the other sheet (lets call it Transactions) in each column
- So for the Budget column, the formula will be
=SUMIFS({Transactions Budget},{Transaction Project},Trades@row)
{Transactions Budget} will reference the entire [Budget $] column of the Transactions sheet
{Transaction Project} will reference the entire [Project] column of the Transactions sheet
Do the same thing for the [Actuals $] column
Use the Reference Another Sheet link when entering the SUMIFS formula, it should appear when you are trying to enter the range.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Hi,
Depending on how many metrics you need you could use the sheet summary, but if there are many and you want to calculate further on them you'd need to add them in the sheet somewhere or on a so-called Master Metrics Sheet.
Try something like this. Replace the BUDGET $ to actual for that formula.
=SUMIF(PROJECT:PROJECT; "Plumbing"; [BUDGET $]:[BUDGET $])
The same version but with the below changes for your and others convenience.
=SUMIF(PROJECT:PROJECT, "Plumbing", [BUDGET $]:[BUDGET $])
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Did it work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Frank and Andree,
Your advice is helpful and I am just trying to get it right.
Kind regards
-
Happy to help!
Glad we got it working!
✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå I thought of that but realised there would need to be too many.
I did not want to include in the answer as it may have been confusing.
Good to have it here though for others to reference.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
@Frank Falco I figured it wasn't that many, but as you say, it's better with more options.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
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!