Creating Sumif in Airtable

[Chelsea_Boss1]

I am attempting to perform a task in which I’d typically use a Sumif formula. I’m trying to “sum” the extended price “if” the sales rep column equals the reps name.

For example:
Extended Price Sales Rep
$400 Mike
$250 Justin
$150 Mike

Result:
Mike = $550
Justin = $250

Is there a formula / work around for this?

[Loic_Sanchez]

Hi @Chelsea_Boss1,

I see this is your first post, so welcome to the community!
In your case, I would have a table with the sales reps, and a table with the sales.
The sales rep for each sale would be a link to the sales reps table.
Inside the Sales reps table, you can then add a ‘Rollup’ field with a SUM(values) formula on the Price column of the Sales table.

See the link below to a text base where I did that:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[James_Lanius]

Sorry to reply to an old thread, but I’d like to build on this.

What if each item in the sales table had a sales rep AND a Client
$400 Mike Client A
$250 Justin Client B
$150 Mike Client B
$200 Justin Client A

And I want to SUM the sales figures per rep, per client?
So a two dimensional lookup, you know?

In Excel I can do =SUMIF(argument 1, argument 2, argument 3…)
Can I do that in Airtable?

For the record, this is actually to compare Expenses per Project per Account to Budgeted amounts. I’m just riffing off the sales rep thing.

[Andrew_Brown1]

I also have a use case for this - I’ve got a lot of reports from different technicians linked to a “Months” table. The challenge is, I want to sum up the total reports per technician per month, and the current formula setup won’t allow this without a lot of manual work to check each and every one.

Does anyone know of a workaround for this?

[W_Vann_Hall]

When you say

Andrew_Brown1:

I want to sum up the total reports per technician per month

do you mean you want to count how many reports each technician filed, or do you need to sum a per-report value per technician?

If the former, it’s trivial. I’m assuming you don’t have a per-technician linked record; instead, you have

• a record per report
• a field (single-select, preferably, or single-line text) containing the technician’s name
• a linked record from the [Reports] table to the [Months] table indicating in which month the report is filed

If so, do the following:

1. Create a rollup field in [Months] that follows the link back to [Records] and referecnes the {TechnicianName} field using an aggregation function of ARRAYJOIN(values). Call it {TechnicianString}.
2. Create a formula field in [Months] for each technician using the formula

   (LEN(
       {TechnicianString}
       )-LEN(
           SUBSTITUTE(
               {TechnicianString},
               'TechnicianName',
               ''
               )
           )
       )/LEN(
           'TechnicianName'
           )
   

(Obviously, replace 'TechnicianName' with the name of the technician whom you’re trying to tally.)

What this does is take the difference between the length of the list of all technicians who filed reports for that month minus the length of the list of all technicians filing for that month except for the technician in question and divides it by the length of the technician’s name. The result is the number of reports filed by that technician.

This does require you define a formula field for each technician reporting during that month; I know of no way around that.¹

If you need more than a simply tally, there are ways of obtaining that, too — but the process is much more complex

---

1. At the least, you have to have a formula that takes into account each technician to be tallied. For instance, you could concatenate all of the technician tallies for each month by wrapping the formula shown above with some additional verbiage and connecting them with '&':

'Technician 1: '&
   (LEN(
       {TechnicianString}
       )-LEN(
           SUBSTITUTE(
               {TechnicianString},
               'Technician 1',
               ''
               )
           )
       )/LEN(
           'Technician 1'
           )&' reports\n'&
'Technician 2: '&
   (LEN(
       {TechnicianString}
       )-LEN(
           SUBSTITUTE(
               {TechnicianString},
               'Technician 2',
               ''
               )
           )
       )/LEN(
           'Technician 2'
           )&' reports\n'&

[so on]

Regardless, I know of no method written entirely within Airtable that would allow you to break out per-technician totals for an arbitrary collection of technicians; instead, you will need to extract totals explicitly per technician. (If anyone else has a solution, I’d love to be proven wrong.)

[Justin_Barrett]

What about grouping? Group by the month links, then group by technician.

If you need to actually end up with data in a field that you can put into a report of some kind, this won’t get you there, but you can see the report-per-technician tally right next to their name.

[W_Vann_Hall]

Well, yeah, that would be the smarter way of doing this. :winking_face:

[Greg_B]

Hi guys,

I’m having a similar challenge to solve and I can’t find a proper way it seems.

I have 4 tables: Clients, Projects, Transactions, Items.
The context is a company lending different types of scaffolding to some clients.

Each project is a construction site and have a linked record to the clients table and the transactions table. One project have many transactions.
Each transaction have a linked record to the items table. Only one item per transaction, and there’s a “QTY movement” with positive or negative number depending if it is for a delivery or a collection.
In the item table we have a rollup from the transaction table on the “QTY movement” with a SUM(values) and a “QTY in warehouse” with a formula “0+{QTY movement}”, and this is working fine.

Now what we want is to know if the client gave us back all the scaffolding we lent to them (or too much as it happens sometimes cause they are also borrowing materials from other companies…), and the difficulty is that a project can last for months and there can be a lot of deliveries happening and a lot of collections (for a single item and also for many items within a project).

So for that last point, in the project table, we did a rollup from the transactions table on the “QTY movement” with a formula saying:
IF(SUM(values)=0,”Balance Matching”,
IF(SUM(values)<0,”SUM(values)&” Missing”,
“+”&SUM(values)&” Over”))

But the balance matching can be wrong in a project:
If we have a delivery of -9 Item A,
A delivery of -1 Item B,
And a collection of +10 item A.
Then it will say the ins and outs of the project are matching, but in fact the client gave us back 1 too much of Item A and still haven’t gave us back the Item B.

That is the main challenge, and for the “missing” and “over” we don’t know for which items within the project.

So what we would need is a balance per project per item.
Does anyone know of a workaround for this?

@Justin_Barrett @W_Vann_Hall @Loic_Sanchez

[Justin_Barrett]

My gut feeling is this: change the records in your [Transactions] table so that each one tracks both the outgoing and incoming quantities for a single item on a single project. The fields might look something like this:

• {Project} - link to a record in the [Projects] table
• {Item} - link to a record in the [Items] table
• {Quantity Out} - The number that went out
• {Date Out} - The date the above number went out
• {Quantity In} - The number that were returned
• {Date In} - The date of return
• {Balance} - formula; the difference between {Quantity Out} and {Quantity In}
• {Status} - formula; some kind of message based on the {Balance} value

That way the tracking of in vs out is kept at the transaction level. You could add a view that only shows items that are out (i.e. they have no value for either {Quantity In} or {Date In}) to make it a little easier to find them when recording returned items. The project record can then roll up the results of all transactions for summary display.

[Sasha_Chernysho]

I have a creative work around that may help someone. My use case is a budget sheet where I have 2 variables to roll up the avg for, the category and the month. I have a categories table that has a field returning the avg for each category, and a monthly budget table that has each individual transaction. If there is one transaction per month per category, avg is easy. But some categories have multiple transaction per category per month, and I dont want to create a third redundant table just for this.

So, in the category table with the roll up field, I set the formula to sum(values)/12, with a filter of values within the last year. Its slightly less accurate than taking an exact sum for each month before avg, but for my purposes it is close enough. Essentially, any extra transactions are divided by all the months and the avg is taken that way. If you are not dealing with months, you can number each field type (related to the reps example, each rep would be assigned a number “ID”) and then divide sum(values)by number of reps (or other variable). May not be ideal, or solve all issues, but will return a number that can then be used in other reports. If your “divider” is constantly changing (new month, new rep etc) you can create field in the same table that all possible reps/months/etc link to, and just count the fields, and do a look up of the count, and divide by that in the roll up category.

Hope this logic helps someone!