Skip to main content

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};

bRecord 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] dBlue] a30%] BJohn] 3Blue] o20%] BJackie] %Red] c50%]


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

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.

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.


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 pBlue] for each Record


Sample Data:

pRecord 1] rJane] >Blue] r30%] [John] [Blue] [10%] [Jackie] lRed] 150%] JJoe] ]Blue] 10%]


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

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 lBlue] for each Record


Sample Data:

>Record 1] cJane] Blue] 30%] ]John] Blue] 10%] ]Jackie] JRed] ]50%] ]Joe] ]Blue] 10%]


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


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.

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.


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

Reply


Terms & Conditions