Skip to main content

Calculating Subtotals based on Single Select Drop Down column

  • February 15, 2019
  • 3 replies
  • 64 views
A
Forum|alt.badge.img+2

Hi, I’ve searched around and haven’t found an answer to this, so here’s hoping someone can offer help.

I’m using AirTable to record and invoice hourly work. Among my columns I have

  • client (organized in single select dropdown)
  • employee (organized in single select dropdown)
  • employee hours (organized in a # column)
  • date

I need to create a view or a tab in my base that adds all the total hours by client in a way that allows me to add them to a Block so I can create an invoice.

For example:
a) On (date) (Steve) worked (3 hours) on (client 1)
b) On (date) (Steve) worked (2 hours) on (client 2)
c) On (date) (Bob) worked (4 hours) on (client 1)

I need to have this formula calculate
Client 1 - 7 hours (sum of records A & C)
Client 2 - 2 hours (record B)
… ideally while filtering by employee and date, but I can figure that part out.

Obviously, organizing a view into “Group By” accomplishes this, but the sums of the Group By fields are not possible to import as a value into a Block.

Anything helps, thank you!

Justin_Barrett

3 replies

Justin_Barrett
Forum|alt.badge.img+21

The grouping operations will probably get you a lot of what you want. Make a new grid view, and in that new view choose to group by client. That will automatically organize everything into collapsable groups, with the Employee Hours column auto-summarized for each. For subgroups, add another grouping criteria like Employee, so you can then see an overall summary for each client, and within that sub-summaries for how much each employee worked for that client.

A
Forum|alt.badge.img+2
  • Alexandra_Piotr
  • Author
  • New Participant
  • February 15, 2019

The grouping operations will probably get you a lot of what you want. Make a new grid view, and in that new view choose to group by client. That will automatically organize everything into collapsable groups, with the Employee Hours column auto-summarized for each. For subgroups, add another grouping criteria like Employee, so you can then see an overall summary for each client, and within that sub-summaries for how much each employee worked for that client.


Thanks Justin. I actually just amended my original post because I realize I didn’t specify-- I need to have these sums be in a cell so I can automate an Invoice in Blocks. Setting the “group by” filter does this, but it’s not translatable to a block.

Thanks anyway for the response!

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

Ah, that makes sense. I messed around with a sample base and a quick temp block to act as an invoice. (Haven’t used blocks before, but it was fun playing for the first time.) Here’s what I came up with.

I’ve got three tables: Work, Clients, and Employees. Work is where the date, client, employee, and hours are entered. However, unlike your example, the client and employee fields are links to their respective tables (single record links only).

The bulk of the work happens on the Clients table. The automatically-generated linked field from the Work table, I renamed “Dates Worked.” I then added a Rollup field named “Hours Worked.” This points to the linked records in the Dates Worked field, and looks up the Hours field from each of those records, using the SUM function to aggregate them, creating a total number of hours worked for each client. I added another Rollup field named “Employees,” also targeting Dates Worked, this time pulling from the Employee field of those records, and using the ARRAYUNIQUE function to aggregate them. From there the data can be pulled into a block as you build your invoice.

Does that work for what you need?

Terms & ConditionsAccessibility statement