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