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

[Melissa_Bradley]

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?

[Mohamed_Swella1 · Accepted solution]

Hi @Melissa_Bradley,

Welcome to Airtable Community! :slightly_smiling_face:

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

[Melissa_Bradley]

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)

[Mohamed_Swella1]

You’re welcome @Melissa_Bradley

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

[Melissa_Bradley]

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!

[Mohamed_Swella1]

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?

[Melissa_Bradley]

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?

[Mohamed_Swella1]

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.

[Melissa_Bradley]

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

[Mohamed_Swella1]

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