Rollup on condition?!

[Kosta_Kondraten]

I want to create a conditional rollup field that only calcualtes the sum of values that are marked as Completed (so there’s a column with a checkbox - I only want to calcualte the totals if the row has that checkbox selected with a tick) - Makes sense?

How do I do that? I know I can do that in Excel right? Can I do it in AirTable?

[Mohamed_Swella1]

Hi @Kosta_Kondratenko,

The way to do so is make an IF statement in the original table and you can then rollup the answer to the IF statement.

However, if you rollup the checkbox with a Sum value, it should give you your required data.

BR,
Mo

[Kosta_Kondraten]

Thanks but ht eissue is that every value is going to be different so I can’t just sum on the checkbox. I’ve never tried if - so you’re saying I could have a column in the original table that will have a value from the column over ONLY if the checkbox at the end is checked?

[Mohamed_Swella1]

Hi,

I thought you wanted to count not sum the values. To sum the values you can use the IF formula

IF(Checkbox=1,Value,0)

You can then rollup this field.

BR,
Mo

[kuovonne]

If you want more background on Mohamed’s answer, try reading the support article on conditional rollups.

You create a new formula field in the table with the values: IF({completed}, {value}) Then you create a rollup field based on the new formula field.

[Kosta_Kondraten]

Yep that’s the way to do it - smart. And I can hide that field too.

[Kosta_Kondraten]

kuovonne:

IF({completed}, {value})

I fcking love AirTable. This sht is miraculous - does exactly what I needed it todo. The only thing is no point having those curly braces { } etc, that just complicates things and doesn’t pass the parser. You just do:

IF(Field1 = 1, Field 2)

That’s it, works like a charm for me :slightly_smiling_face:

Where F

[Katherine_Duh]

Hi folks! We’ve just introduced a feature that should hopefully make these kinds of workflows easier in the future. You can now set custom filter conditions on lookup, rollup, and count fields from within the field customization menu, so that it will only show the specific records you’re interested in. You can learn more from our announcement post:

Launched: conditional relational fields (lookup, rollup, and count) Announcements

You can now add custom filter conditions to lookup, rollup, and count fields to limit which records are included in the results. Here’s how to do it: Create a valid lookup, rollup, or count field (if you don’t already have one in your table, you’ll need to create a valid linked record field first). In the field customization menu, turn on the “Only include records which match certain conditions” toggle. Specify the relevant filters for your use case. [Screen Shot 2020-04-28 at 10.50.22 AM] […

[TawfeeqSaad]

Hello kuovonne, is there away to have rollup sum values with dynamic filter ie. instead of summation for invoices for one customer ID, I want to have summation for all invoices for each customer, so I don't need to enter a static customer Id in the "Only include linked records from the Visits table that meet certain conditions", in this case, it will sum all relevant invoices for each unique ID field

[afcap]

Hello all - I'm bumping this thread as I think I may have the same issue as TawfeeqSaad, and I'm not sure whether there's a way of addressing this issue.

I have two tables: records in Table 1 correspond to products, and records in Table 2 correspond to sales of products.

I'm trying to create a single roll-up field in Table 1, so that for each product record, it gives me the sum of all revenue generated from all sales records in Table 2 which correspond to the product in question.

I seem to understand that the conditional rollup formula will only allow me to sum records in Table 2 for which a particular record (say, "Product Type") equals a fixed value (e.g., a single-select). I am trying to set up a rollup formula which will execute different sums according to the different values that could show up in a particular record (e.g., different Product Types for each individual sales record).

For example: Table 1 has 2 product records: Books and CDs. Table 2 has 10 sale records: 4 for Books and 6 for CDs. In Table 1, I want to create a single rollup fields which (a) for Books, will sum revenue from all 4 sales records for which the Product Type = Books, (b) for CDs, will sum revenue from all 6 sales records for which the Product Type = CDs, (c) and so on.

Is this feasible - whether by existing functions or a clever work-around?

Thanks in advance!