Skip to main content

Sum of $ column when other column values match

  • April 10, 2018
  • 2 replies
  • 35 views
N

Hello! I have a table like:

Name Project Labor Days
Donald Name1 $150 Sun, Mon, Tue
Joe Name2 $130 Sun, Mon, Tue
Alex Name1 $130 Sun, Mon, Tue, Wed, Thu
Cookie Name1 $130 Sun, Mon, Tue, Wed, Thu
Alicia Name2 $130 Mon, Tue, Wed, Thu, Fri
Doris Name1 $130 Wed, Thu, Fri

Now in Excel I can easily do this in a separate table worksheet:

Sun Mon Tue Wed Thu Fri Weekly
Name1 $410 $410 $410 $260 $260 $130 $1,880
Name2 $130 $260 $260 $130 $130 $130 $1,040

Thus, I can do math based upon value. I want to be able to draw from my schedule column (Sun, Mon, Tue, Wed, Thu, Fri) and get the amount of labor (predefined amount of $#, consistent among everyone so it doesn’t need to be different per line) for I can print out the total it’d cost me on each day of the week, and a grandsum of the weekly total for each project, depending upon what’s in the project field.

I am not sure how to do this, as AirTable doesn’t make calling from other sheets/table easy like Excel does.

      2 replies

      W
      Forum|alt.badge.img+5
      • W_Vann_Hall
      • Inspiring
      • April 10, 2018

      Ah, the spreadsheet metaphor: Airtable’s strength and weakness.

      A good rule of thumb is that when you look at a table in a grid view — the one that looks most like a spreadsheet — the only calculations you can perform must use values along a horizontal line. To perform any math or comparisons against values arrayed vertically, you must link the records (the horizontally oriented values) to a record in another table and use this linked record as a point from which to gather and consolidate values from other records.

      For instance, in your example above, you might easily have individual tables named [Name], [Project], [Labor], and———

      Wait: Take a look here. I copy-and-pasted the four columns of data (Donald through Doris through ‘Wed, Thu, Fri’) into an Airtable table, converted {Project} and {DoW} from single-line text to linked-record fields (creating the linked tables and automatically generating the linked records), and tossed together a few rollup fields to perform some of the consolidating math. (I didn’t do a cost per DoW per project, because I’d need to change the base structure a bit, and I don’t really wantto get neck deep in an illustration.)

      That’s a bare-boned base, but you should be able to build upon it to achieve the sort of functionality you outline above. Will it be as easy as working in Excel’s two-dimensional matrix? No. Will you be able to accomplish things you can’t do in Excel? Yes. Do you need to accomplish such things? I don’t know; only you can answer that question. Airtable, like any tool, isn’t the right choice for every application; this may be one of them. However, if it is the right choice, Excel isn’t. :winking_face:

        N
        • Nick_Burrus
        • Author
        • Participating Frequently
        • April 10, 2018

        Ah, the spreadsheet metaphor: Airtable’s strength and weakness.

        A good rule of thumb is that when you look at a table in a grid view — the one that looks most like a spreadsheet — the only calculations you can perform must use values along a horizontal line. To perform any math or comparisons against values arrayed vertically, you must link the records (the horizontally oriented values) to a record in another table and use this linked record as a point from which to gather and consolidate values from other records.

        For instance, in your example above, you might easily have individual tables named [Name], [Project], [Labor], and———

        Wait: Take a look here. I copy-and-pasted the four columns of data (Donald through Doris through ‘Wed, Thu, Fri’) into an Airtable table, converted {Project} and {DoW} from single-line text to linked-record fields (creating the linked tables and automatically generating the linked records), and tossed together a few rollup fields to perform some of the consolidating math. (I didn’t do a cost per DoW per project, because I’d need to change the base structure a bit, and I don’t really wantto get neck deep in an illustration.)

        That’s a bare-boned base, but you should be able to build upon it to achieve the sort of functionality you outline above. Will it be as easy as working in Excel’s two-dimensional matrix? No. Will you be able to accomplish things you can’t do in Excel? Yes. Do you need to accomplish such things? I don’t know; only you can answer that question. Airtable, like any tool, isn’t the right choice for every application; this may be one of them. However, if it is the right choice, Excel isn’t. :winking_face:


        Your explanation and example helped me tremendously. While it would require a reconstruct of my entire base with a ton of data in it, to reflect those. But, you made me realize, I could simply link to the records in another table. Hide the column if I didn’t want the data to be viewed, but, by linking, I can pull the data through an @rollup and through LENS. Thank you!

        I need to stop thinking of this like a spreadsheet. Ten years of Excel will do that to you, right? I just managed to throw together a basic version of my ultimate idea, and if I make any changes, AirTable will automatically adjust any and all values. This is something that used to take me 10 hours a week to do, now, once set up, will be less than five minutes.

        Terms & ConditionsAccessibility statement