Summing two columns based on two column values

Platform discussions
,
1

I’ve got a board where each item in the board is a project. Each item has two parts: Part A and Part B.

We’ve got a column for the cost of Part A in dollars, as well as the cost of Part B in dollars. We’ve also got a list of teams that could work on Part A or Part B interchangeably, so we have two columns that represent which team worked on each part: Part A Team and Part B Team.

I’d like to find a way to sum the corresponding cost columns for a single team. For instance, I want to add all the values in Part A Cost where Part A Team is “John”, as well as all the Part B Cost values where Part B Team is “John”. So if Part A Team is “Jane” and and Part B Team is “John”, I should only attribute the Part B Cost to “John” here. I’d also like to try and do this without subitems.

It’s very easy to create two bar charts where each one would use a team as the X axis and the cost as the Y axis, but I can’t seem to figure out a way to sum those values for each team without having to do it manually.

Any ideas? Is this doable without subitems?

2

Hey there!

Are Part A and Part B numbers columns? Or what kind of columns are you using to differentiate Part A and Part B, if not subitems?

If they’re numbers columns, you should be able to apply board filters by person and then the sum at the bottom of the numbers column will adjust to reflect what value you filtered by.

For example, if you had a column with 50, 40, 22, 13 but only the 40 and 13 could be attributed to John, when you apply a filter to only show the items where John is assigned, you will see the sum tab at the bottom of the column reflect 53.

You can then save the filtered view as a new board view.

Does this help with what you are looking to do? Or would you prefer to display the information/sums in a different way than just a table view?

Let me know what you think!

3

Hi Charlotte! Thanks for taking the time to assist.

You’ve got it dead-on: Part A Cost and Part B Cost are numbers columns. But there’s also Part A Team and Part B Team.

I need to sum all the Part A Cost where Part A Team is “XYZ” as well as all the Part B Cost where the Part B Team is “XYZ”.

Here’s an example table:

Part A Cost | Part A Team | Part B Cost | Part B Team
------------|-------------|-------------|-------------
        $25 |     John    |         $70 |     Alice
        $70 |     Alice   |         $25 |     John
        $50 |     John    |         $50 |     John

So in this case, I’d want to see a sum per team of Part A Cost and Part B Cost, so ideally I would have a chart with the different teams on the X axis (only two in this case: Alice and John), and the Y axis would have values that are indicating that John had a total of $150 cost, and Alice had a total of $140 cost.

Any ideas?