Conditional rollup problem

[Little_Shiva]

Hello! I’ve built a base to track one consultant’s hours and expenses for several different clients. The table I need to draw data from is the Hours and Expenses table.

Client / date / rate is the ID field, then we have date, hours worked, a lookup field showing euros per hour, and a formula field showing billable hours in euros.

Other fields I need to be able to summarize by month are the billable kilometers driven and the billable expenses.

The consultant has a form he fills out to populate every record: he enters the date, his hours, his kilometers, and his expenses for about ten different clients, plus descriptions (either single-selects or text) as needed.

At the end of each month, we need to be able to see how much to bill each client. Is there a way to have all the hours worked records automatically summarized, without anyone having to go nitpick through a list one by one? Same question for billable km’s and expenses. I have a “billable?” checkbox field for the consultant to check, because sometimes things are NOT billable and we need to be able to separate those items out.

Is this even possible? If so, how?

[Vivid-Squid]

Hi @Little_Shiva
Yes this is all very possible in Airtable. I can get you started with a very simple version of what you asked for.

Here is my Client table layout:

Here is my Hours Worked/Expenses Table:

Yours may be a little different but the basics are the same

Here are the roll ups and formulas I used:

Get the name of the month from the hours/expense date

The Hours worked roll up in the Clients table. Notice the condition that Billable should be checked for it to roll up.

Now you can create a view for each month or a grouping to see the total for the month

The rest of the items should follow suit as the hours worked.

[Little_Shiva]

Oh, wow! Thank you! I’ll study this and see if I can make it work.

Little Shiva
littleshiva.com
streetcatsofvis.com

[Little_Shiva]

@Vivid-Squid, would you be willing to look at screenshots of my base in a private message to help me troubleshoot? I’m having trouble following your instructions and wonder if I built my base incorrectly.