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,
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.
Hi @Jean_Pierre_Traets , Thank you for the quick reply!
Two issues with your suggestions
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…
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.
Please share an actual doc with some dummy content and what you actually look for to achieve.
Preferable in English
Hi @Jean_Pierre_Traets .
I attached a link to the shared doc (In English ) 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 -
Hope it is not too complicated…
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?
First of all, thank you for taking the time to answer despite your busy schedule!
Regarding the solutions you suggested:
- 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…
- 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?
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
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.