Rollups with conditional option?

[Markus_Wernig]

I am trying to solve the following problem:

One table lists all our available products, another table lists projects/jobs in which our products were used and sold for. A rollup on the inventory table counts how many times each product was sold.

But here is the problem - in the project/job table I have an option column that allows me to list a job either as “bid” or as “actual sale”. A custom view in that project table allows me to easily view bids and sales, but the rollup in the inventory table counts both options, bids and sales.

Is there a way to tell the rollup to only count sales and ignore bids? That to me sounds like a pretty standard database function, but I cannot figure out how to do it in Airtable.

I am really grateful for any advice.

[Howie]

Markus – the way to do this right now would be to create another field on the “Jobs” table that is a formula. You could make it something like this:
IF(Type = “Sale”, Amount, 0)

So this field would be filled with either the sale amount (duplicating the actual “Amount” field), or zero, depending on whether the job was a bid or an actual sale.

You could then apply the rollup field on that formula field, rather than the “Amount” field directly. Does that make sense?

[Markus_Wernig]

Thanks Howie,

that makes sense for counting the money coming in. But what I want to do is count the number times our products were actually used/sold. They can be resold again and again - since we are only selling licenses that are not exclusive.

On the jobs table I list all the (linked) products that were used in a particular job and since they can be sold/licensed many times they show up multiple times in various jobs. On the inventory table, there is a column (rollup) showing a number that tells me how many times a specific product has been sold/licensed. On the same table I also have a column that lists all the jobs each product was used in/licensed for. But right now that also includes bids.

Could your solution also work for what I am trying to do?

Thanks in advance.

[Howie]

Yes, you can achieve that with the same formula + rollup approach. Feel free to email support@airtable.com if you’d like to jump on a screenshare!

[Markus_Wernig]

I just did. Thank you in advance.

[Christopher_Bis]

I have a similar, but slightly different question:

I need to be able to rollup only the most recent submission. I could use your suggested answer to @Markus_Wernig if I could use full spreadsheet-like formulas eg:
=IF( Date = MAX( FILTER( Date, Name={THIS NAME} ) ) , Availability, “”)

Is this capability planned? Do you have any other suggested work-arounds? Is it possible to roll-up only the most recent submission?

[Manda_Ganbold]

But aggregate fields such as roll-up, count and lookup, need to have a condition able to compare with the values in any column of the linked table. Adding field is not a suitable approach in case of multiple values to roll-up, for it is time consuming for structuring the base.

[Heracles001]

Arrived here from Google search, so posting for everyone that follows:
this is now a feature → https://support.airtable.com/hc/en-us/articles/360046636794-Conditional-counts-lookups-and-rollups