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])
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.
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).
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?
Hi @Jean_Pierre_Traets.
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?
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
) for easier reference at the bottom.
