Sumif with two variables

Hello everyone.

I’m having some issues with the SumIf function.
The situation is as follows:
I have a table in which i count working hours. Each row consists of 3 inputs (coulmns) that are of significant - Project related / milestone / amount of hours (Table name - “main_hours_table”)

I made another table in which I’m trying to have a column that sums all the hours of a specific milestone, for a specific project (I need a different table q view for every project).
(Table name - "hours_per_project)

I made a column in the “hours_per_project” table with the following formula:
[main_hours_table.hours_count].sumif(thisrow.[milestone]=[main_hours_table.milestone] and (thisrow.[project]=[main_hours_table.project])

But it doesn’t work…

Appreciate your help,
Ranny

image

1 Like

Dear @Ranny_Shor,

I didn’t have the chance to test the formula, but based on the screenshots, I suggest this formula should do the trick:

This is what the formula will do:

Have a look at the “main_hours_table” → filter on milestone column where the milestone is the same as in this column → sum the hours.


main_hours_table.filter(milestone=thisRow.milestone).hours.sum()
1 Like

Hi @Jean_Pierre_Traets , Thank you for the quick reply!

Two issues with your suggestions

  1. In the [main_hours_table], the column [milestone] is a lookup column from a different table consisting all the possible milestones for the projects (called [milestones].
    The [hours_per_project] table is a view of the [milestones] table. If I understand correctly - the formula is not working because one of the columns is a lookup column…

  2. Even if the 1st issue was resolved - The formula you specified handles only one variable which is the milestone.
    But the “main_hours_table” collects data of different projects and I want the formula to only sum the rows that are relevant to a specific project AND has the same milestone.

For example - added again the [main_hours_table] with different entries for different projects. I want to sum only the rows that have the same milestone and relevant to a specific project.

Thx,
Ranny

image

1 Like

Dear @Ranny_Shor,

Please share an actual doc with some dummy content and what you actually look for to achieve.

Preferable in English :relaxed:

Hi @Jean_Pierre_Traets .
I attached a link to the shared doc (In English :slight_smile: ) for easier reference at the bottom.

What I whish to achieve is a column that sums up hours that match a specific milestone AND a specific project.

  • In the doc there is a page called [Main Hours Table]. This page consists of different hour entries for different milestone related to different projects (inputs made using a button called [Hour input] on the same page).
  • The different milestones are organized in a table on the same page called [Milestone List].
  • The next page is called [Projects] and in it there are two sub-pages of different projects.
    The main project table is called [project list] and it has the project name and all the milestones relevant (a lookup column from the [Milestone List] table).
  • Each project has its own hours table which is a view of the [Milestone List] table.

I want the tables inside each project page to show the sum of all the hours accumulated for a specific milestone and for that specific project, so I could compare the number to the total hours planned for that specific milestone.

So specifically I’m looking for the right formula in the “hours_sum_per_project” column (unless there is an easier way that I’m not aware off).

Link to the shared doc -
https://coda.io/d/dsipXVQWje2/Main-Hours-Table_sueY

Hope it is not too complicated…
Thx,
Ranny

Dear @Ranny_Shor,

I am on a business trip this week, and therefore less available online.
Please have a look at your doc, where I have put a filter to get the results with the support of a “chaining” formula. (The way how Coda solves it)

I have made also a view that does the same without formulas, but with the support of grouping and summarizing

Please let me know if this is what you are looking for?

2 Likes

Hi @Jean_Pierre_Traets.
First of all, thank you for taking the time to answer despite your busy schedule!

Regarding the solutions you suggested:

  1. The grouped and summarized view does not allow me to add another column with the hours planned and compare between the two columns. At least I’m not aware of such a possibility…
  2. The formula you added summarizes the hours of a specific milestone for all projects. I need to filter each milestone for each project.

I have added another table that I think is simpler and more accurate in the way that is it structured.
It is in the [project]>[W23] page.

The table has 2 lookup columns taking data from the [main_hours_table] (milestone & project).
I added another column with a formula that is supposed to sum the [hours] column for all rows that contains both the [milestone] and the [project] relevant. But for some reason is not working…

main_hours_table.filter(milestone=thisRow and project=thisRow.project_PP).hours.Sum()

I feel close but not there yet… Could you show me what I’m doing wrong with this formula?

Thanks again!
Ranny

1 Like

since @Jean_Pierre_Traets is travelling, I had a look. The data set up is confusing, but this logic as below works;

the table you created is hard to work with, the column project_PP is linked to milestones, so you have two columns with milestones and then you need hours. I would urge you to rework your table logic a bit.

I am not travelling, but short in time, cheers, Christiaan

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.