Skip to main content

Sum Ifs in Airtable

M
Forum|alt.badge.img+4

Hi everybody,

During my continued transition from Google Sheets I encountered a problem that I can't really find a solution for (or I am not understanding the other posts on this).

Here's he situation: In Google Sheets I have three sheets - orders, customers and overview. In the latter I could change variables to see different information from the other two, such as the total number of orders for a customer in a given time period. 

To display this, I used an Arrayformular with SUMIFS, so I could check for multiple conditions based on my entries (e.g. "from" date & "to" date, order sum larger / smaller than, etc.). 

I understand that in Airtable "Rollup" seems to be the feature to replace this. BUT: When setting the conditions for these rollup fields, I can not reference to other sheets or fields and have to enter static values. So I cannot say "display all orders after <field A> in same sheet" but have to enter 01/01/2022. 

This is a serious issue for me. Does anybody have an idea on how I can solve this?

    2 replies

    pressGO_design
    Forum|alt.badge.img+19

    You need to use one or more views, interfaces, and/or extensions to structure these kinds of overviews. But first you need to ensure that your data is structured properly and plays nicely together, whether as linked records or automations or integrations with external platforms. In my experience, this is what trips up new Airtable users more than anything else. A database is a very different animal than a spreadsheet.

    Once that's done, you can, for example, create a views in your customers table that filter for multiple conditions. As an example, let's say you want to see a list of customers in the UK who have placed an order within the past 3 months. You create a view, call it "Three Month Window" (or whatever) and filter for Order Date is within past 90 days and Country = UK. That view is always current - you don't need to enter start/stop dates. You can have another view called "Over/Under" that color-codes records based on whether a customer has ordered more or less in the past Z time period than they did in the Y time period before. 

    Usually solving these types of problems requires a business relationship and the ability to see your actual data - and I'm not suggesting that you do that here. What you're asking for is highly dependent on how you've set up your base(s) and it's very difficult to provide a solution that is simply "do this, then that, then the other thing."

    TheTimeSavingCo
    Forum|alt.badge.img+28

    I sort of do, but it's kind of convoluted and fragile I'm afraid

    1. Link all of the records in the Data table to a single record in a "Helper" table
    2. In the Helper table, add fields to contain the data that'll be used to filter the records in the Data table by
    3. In the Data table, create lookups to display the data from step 2
    4. In the Data table, create a formula field that will do the filtering for each record against the values in the fields from step 3
    5. Use the formula field from step 4 for the conditional rollup

    This'll let you add, say, the sum larger value in the Helper table and get a rollup of all the records that had a sum that was larger than the value you set

    Adam, Consultant @ thetimesaving.company

    Reply


    Most helpful members this week

    Terms & Conditions