Skip to main content
Solved

Summarize Data for Reporting (In Excel would use Sumifs summing data on different sheets)

M

Hello,


I am using AirTable for my plumbing company workflow. My base is currently set up with a table for Jobs (each record is a single job with info like data requested, job status, plumber, revenue, etc) and a linked table for Plumbers.


I would like to be able to produce summary reports by week & month. For example, I would like to calculate the revenue generated by plumber for each week into a new record. This way I can summarize with a bar chart showing totally weekly revenue over time with a breakdown by plumber.


In Excel, I would do this by creating a new Summary tab. It would have a table with Plumber Name across the columns and Week Of across the rows. I would SumIf with two arguments (date within a range and plumber name). It would have a final column totally revenue across all plumbers for each week. I could use this data to create various graphs.


How can I recreate this summary in AirTable?

Best answer by Mohamed_Swella1

Hi @Melissa_Bradley,


Welcome to Airtable Community! 🙂


You can use the Rollup field in the Plumbers table to sum the value of work they did in total.


For a barchart, you can use the Blocks to do it directly in the Jobs table and group by Plumbers.


BR,

Mo

View original
Did this topic help you find an answer to your question?

9 replies

M

Hi @Melissa_Bradley,


Welcome to Airtable Community! 🙂


You can use the Rollup field in the Plumbers table to sum the value of work they did in total.


For a barchart, you can use the Blocks to do it directly in the Jobs table and group by Plumbers.


BR,

Mo

M
  • Melissa_Bradley
  • Author
  • Participating Frequently
  • 10 replies
  • June 25, 2020
Mohamed_Swella1 wrote:

Hi @Melissa_Bradley,


Welcome to Airtable Community! 🙂


You can use the Rollup field in the Plumbers table to sum the value of work they did in total.


For a barchart, you can use the Blocks to do it directly in the Jobs table and group by Plumbers.


BR,

Mo


Thanks @Mohamed_Swellam!


How could I use Rollup to only sum jobs within a specific date range rather than summing the entire column? (ie filter the rows that get rolled up instead of summing the entire table)

M
Melissa_Bradley wrote:

Thanks @Mohamed_Swellam!


How could I use Rollup to only sum jobs within a specific date range rather than summing the entire column? (ie filter the rows that get rolled up instead of summing the entire table)


You’re welcome @Melissa_Bradley


You can use filters in the rollup if you want (only include records that fall under certain criteria).

M
  • Melissa_Bradley
  • Author
  • Participating Frequently
  • 10 replies
  • June 25, 2020

Great! Didn’t realize that was a feature. Conditional Rollups look like they will solve my problem!


One more question - I just tried to add a rollup in the Plumbers table, but it will only allow me to roll up field within that table. I need to have the answer to the formula in Plumbers, but the formula should be working on data in Jobs. Am I missing something in how I’m setting up the rollup?


Really appreciate your help on this!

M
Melissa_Bradley wrote:

Great! Didn’t realize that was a feature. Conditional Rollups look like they will solve my problem!


One more question - I just tried to add a rollup in the Plumbers table, but it will only allow me to roll up field within that table. I need to have the answer to the formula in Plumbers, but the formula should be working on data in Jobs. Am I missing something in how I’m setting up the rollup?


Really appreciate your help on this!


Basically rollup “rolls up” the data that is linked in another table to this table. So if you have a job for $100 linked to Plumber X and another job for $50 also linked to him you will see this $150 in his record .


Are you trying to do something else?

M
  • Melissa_Bradley
  • Author
  • Participating Frequently
  • 10 replies
  • June 25, 2020
Mohamed_Swella1 wrote:

Basically rollup “rolls up” the data that is linked in another table to this table. So if you have a job for $100 linked to Plumber X and another job for $50 also linked to him you will see this $150 in his record .


Are you trying to do something else?


I must not have my linking set up correctly then. Here are screenshots with an example customer.


You’ll see Plumber Assigned is the field in the Jobs table that links to the Plumbers table.


When I try to add the rollup in Plumbers, I only have the option to rollup fields in the Plumbers table - not the Jobs table.


Any suggestions?


M
Melissa_Bradley wrote:

I must not have my linking set up correctly then. Here are screenshots with an example customer.


You’ll see Plumber Assigned is the field in the Jobs table that links to the Plumbers table.


When I try to add the rollup in Plumbers, I only have the option to rollup fields in the Plumbers table - not the Jobs table.


Any suggestions?



Yes, you have to Rollup the field Assigned Jobs, since it will look for the info in the table which this field is linked to.

M
  • Melissa_Bradley
  • Author
  • Participating Frequently
  • 10 replies
  • June 25, 2020

Oh my gosh, I was not realizing how the Rollup configuration worked! Now I’ve got it! Appreciate your help.

M
1 person likes this
  • M
    Mohamed_Swella1
M
Melissa_Bradley wrote:

Oh my gosh, I was not realizing how the Rollup configuration worked! Now I’ve got it! Appreciate your help.


Anytime 🙂 please mark my reply as Solution so others can benefit from it

K
1 person likes this
  • K
    Kate_Kordsmeier

Reply


Most helpful members this week

Terms & Conditions