Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- SUMIF / Horizontal Rollup

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
3151
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 28, 2020 02:04 PM

Hi! I need help figuring out a formula that will do a rollup of a horizontal range in an Asset database. SUMIF would be a simple solution if I were using excel/gsheets. Creating a separate table to do a vertical rollup is not an ideal solution, but I’d settle if it is the only way. If anyone can help point me in the right direction, I’d appreciate it! Here’s a sample set of my table fields:

[Record 1], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 Name}, {Employee 2 Affiliation}, {Employee 2 Allocation %}… {Employee 6 Name}, {Employee 6 Affiliation}, {Employee 6 Allocation %}, {Affiliation Sum};

[Record 2], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 Name}, {Employee 2 Affiliation}, {Employee 2 Allocation %}… {Employee 6 Name}, {Employee 6 Affiliation}, {Employee 6 Allocation %}, {Affiliation Sum};

[Record 3], {Employee 1 Name}, {Employee 1 Affiliation}, {Employee 1 Allocation%}, {Employee 2 Name}, {Employee 2 Affiliation}, {Employee 2 Allocation %}… {Employee 6 Name}, {Employee 6 Affiliation}, {Employee 6 Allocation %}, {Affiliation Sum}

Record = Variable

Employee Name = Variable, but will never repeat more than one time per Record

Employee Affiliation = Variable, an Employee may have one Affiliation for Record 1 but a different Affiliation for Record 2; additionally, Employee 1 and Employee 2 may have the same Affiliation for a given Record; additionally the list of potential Affiliations is finite but long

Employee Allocation % = Variable

Objective: Create a database cell displaying the total Employee Allocation % associated with each unique Affiliation for each Record

Sample Data:

[Record 1] [Jane] [Blue] [30%] [John] [Blue] [20%] [Jackie] [Red] [50%]

Key Result: {Affiliation Sum} displays “Blue 50%, Red 50%”

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 28, 2020 02:40 PM

Welcome to the community, @Frank_Reagan! :grinning_face_with_big_eyes: This is definitely an interesting challenge. How many possible options are there for the affiliation fields? Your example only mentions two, but my gut says the actual list is larger.

This is probably doable at a technical level, but even with only a few affiliation choices, I wouldn’t recommend trying to create a single formula for the `{Affiliation Sum}`

field. Airtable doesn’t have an equivalent to `SUMIF`

, so we’re left building the sums via more of a brute force method if we use Airtable’s own formula options. Another choice is to use the Scripting block, where the language (JavaScript) is a lot more flexible, although the sum wouldn’t update in real time (unless you were to go the extra mile and use the scripting action beta).

I’d be more inclined to go the scripting route. If you go the formula field route, my recommendation would be to divide and conquer: create individual formula fields that each tally the sum of one affiliation across the six percentage fields, and leave the final `{Affiliation Sum}`

field to aggregate those totals for final display.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 28, 2020 03:16 PM

Thanks, Justin! There are about 40-50 potential Affiliation options that may become necessary over time, however, there are only about 5-10 that are absolutely necessary to have today. I’ll ask one of my co-workers to take a crack at a JS solution via the scripting block, but if there is a way to solve this with the brute force method, I am up for it!

The divide and conquer route with the formula sounds really close what I am ultimately looking for, and is precisely the part that I am getting hung up on without the presence of SUMIF. So, how can I tally the sum of one affiliation across the six percentage fields? If you can help me solve this, I don’t mind creating a field that concatenates results and exclude blanks.

Here’s a new set-up, if you have a solution, that’d be really helpful!

Objective: Create a field {Total Blue} that displays the total Allocation % associated with a specific Affiliation [Blue] for each Record

Sample Data:

[Record 1] [Jane] [Blue] [30%] [John] [Blue] [10%] [Jackie] [Red] [50%] [Joe] [Blue] [10%]

Key Result: {Total Blue} displays “50%"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 28, 2020 03:36 PM

Here’s a formula for the `{Total Blue}`

field:

```
SUM(
{Employee 1 Allocation%} * ({Employee 1 Affiliation} = "Blue"),
{Employee 2 Allocation%} * ({Employee 2 Affiliation} = "Blue"),
{Employee 3 Allocation%} * ({Employee 3 Affiliation} = "Blue"),
{Employee 4 Allocation%} * ({Employee 4 Affiliation} = "Blue")
)
```

Each `{Employee X Affiliation} = "Blue"`

comparison returns True or False, which turn into 1 (True) or 0 (False) mathematically. Multiplying that against the associated allocation percentage either adds that percentage if True, or zero if false. Add all those values together, and you have the total percentage for Blue.

This could also be achieved via a collection of `IF()`

functions:

```
IF({Employee 1 Affiliation} = "Blue", {Employee 1 Allocation%}) +
IF({Employee 2 Affiliation} = "Blue", {Employee 2 Allocation%}) +
IF({Employee 3 Affiliation} = "Blue", {Employee 3 Allocation%}) +
IF({Employee 4 Affiliation} = "Blue", {Employee 4 Allocation%})
```

Neither approach is necessarily better. If you use a plain text editor to edit the formulas, they’ll be a lot easier to build than doing so directly in Airtable.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 29, 2020 07:06 AM

Great, thank you so much for the help and tips! I’ll give this a try.

Reply