Now First I have Test Times, which is basically just a link to table, to do lookups, second I have times, which lookups all times that I entered in a specific table and is linked to a certain employee. So EMP1 has 5 time entries, therefore lookup field “Times” shows me 5 different values. Lastly I have the field “Billable”, which is a Bolean field in the original table and basically just shows if a time entry is marked as billable or not (If billable Bolean field is marked by a check sign). So you see there are 5 times, the first four are checked and the last one is not checked. Is there a possibility to sum only the times that are checked and if yes how?
I already summed all time entries, but I want to sum only the money that are marked as billable in the Boolean field as well.
This basically filters the fieldTimes based on if Billable? is true and then runs sum over the items remaining.
That should do the trick?
FYI, you can also do this without the lookups if you don’t need the lookup fields in that table for anything else. You can replace field in the formula above with lookup and it will lookup the value from the other table using the formula.
I am interested in using the formula similar to SUMIF but I can’t find the way. I have tried with sum(filter) and with if() but I can’t do it. I put it in this conversation because I think it is related to what has been discussed.
The situation is the following:
I have a table where I put the total hours worked in each project. In one day, different amounts of hours are entered in separate rows.
What I want to do:
I would like to sum all the hours in the “Hours Quantity” column, if the “Date” column matches the “Date2” column.
In this way I will have the total hours worked each day. In the end I will have a list with the 365 days and the total sum of hours worked.
As I understand, you have already filtered accepted records into the “Total hours” column.
You just need to sum the values of that column (at the bottom line), and that is
It’s impossible to achieve what you want inside a single table.
But you can do this by adding an additional table and reorganizing your fields a bit.
Here is how I did that:
We need to create the first table “Year presentation”. Here we will have a list of days (365 as you said) and later sum of hours spent on each day will be added here also
Here we need to have just one column, “Date”. Here we need to add each day (screenshot #1)
Then we need a second table, where we will track spent hours on each project (this is similar to the table that you have currently). My table will have the name “Project tracking”.
In that second table we need the same columns as in your table “Project” and “Hours”.
Now we need to link “Year” table into second table. Add new column of type “Link to table”, select our first table, and make sure the checkbox “Create related field…” is checked (screenshot #2)
Now to set the date which we want to track, we need to select a needed day from that linked field (screenshot #3)
In our “Year” table we will have “link to table” column. We need to add another field of lookup type, so select type Lookup, select our Projects table, and field “Hours”.
That’s it! Now we have number of hours near each day. The last step is to add Formula field in our Year table which will sum “Lookup” field (sum(field('Lookup'))). Now we have final version (the last screenshot)
Thank you very much for your answer, I would have never thought I could do it like this, amazing!
Although it is not the best solution for me, since I have a more complex task registration system, where it is important to have the date when the task was performed in a date field and not a list of related days in a table (for example, the task is in a calendar and it needs the date field, or when the task is registered from a form it is more convenient to do it from a calendar and not from a drop-down list with the 365 dates).
Do you know if there is another way using the date field instead of the relationship with a table?
