Help

SUMIF in airtable with variable date fields

Topic Labels: Automations
701 2
cancel
Showing results for 
Search instead for 
Did you mean: 
sftechchick
4 - Data Explorer
4 - Data Explorer

I have two tables, one with a list of payroll dates(vertically) for a company and another that is a payroll download that includes payroll dates(vertically), gross pay and employee information. 

In excel I used the sumif formula to say in sheet 2 sum gross pay for all the rows that equal the payroll start and end date in sheet 1.

Example: There are 10 employees so there are 10 rows with different employees names and gross pay amounts in sheet 2. In sheet 1 I have a single row with the start date 1/1/2023 and end date 1/15/2023. I want to be able to to say sum the gross pay for all the rows in sheet 2 that have a start date of 1/1/2023 and end date of 1/15/2023. I need these dates to be referencing something though so that it will sum different date ranges based on the row it is in. So if the next row down in sheet 1 is 1/16/2023 and 1/31/2023 then it will sum all the gross pay amounts in sheet 2 for that date range. 

I tried the rollup but it is not dynamic enough, and neither is the automations. Is there another way to do this in airtable? 

2 Replies 2

I think the simplest solution would be to use a script for this I'm afraid

A non-scripting alternative is possible but tedious to set up and involves you linking all of the records in Sheet 2 to that single record in Sheet 1 with the start and end date.  You'd then have lookup fields in Sheet 2 to display the start and end date from Sheet 1, and a formula fielda to check if that record's dates was within the lookup start and end dates

You'd then link all of the records which the formula field identified as true to the record in Sheet 1 in another linked field and so if you did a rollup on those links it would give you the sum of all of the records within that date range

Finally, you'd need to clear the original link field in Sheet 1 so that your automation could run for the next record

triple
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a similar issue, however, categories are involved. I would like to recreate a transaction sheet and P&L sheet from Google Sheets into Airtable. In Sheets, there is a "Transactions" sheet with all of the company's expenses. The relevant columns for the P&L sheet are data, amount, category 1, subcategory 1, subcategory  2, and subcategory 3. On the "P&L" sheet, which tracks the transactions by categories and month, I am using SUMIFS equations to get the weekly and monthly totals for each category, as well as the total expenses for each month. For example, this is a snippet of the table. It is continuous for the whole year. 

Screenshot 2023-07-12 at 12.13.44 PM.png

 

I would like to recreate this in Airtable. Right now, I have a "Transactions" table in Airtable. The first field in the date, then a field for the category (Employee, SAAS, contract, office), then a field for the first subcategory (USA salary, PH Salary, Intern, Travel, Sales & marketing, etc.) and then another field for the second subcategory (data research, website, PA, etc). How can I create a P&L sheet that breaks the transactions down by weekly, monthly, and categories, and two subcategories?