How do I Sum If?

Hey @Alex

Thanks for the quick reply! It would help, if I wouldn’t be to blind to find the aggregation function :smiley:! Where exactly is that?

1 Like

Hey @TomBo ,

I feel your pain, it took me a while to find it as well, it’s not super obvious and maybe something we should address UX wise! :smiley:

@jose how do you feel about that?

Either way, the sum function is right here:
sum-function

Thanks! Now at least I dont feel to stupid for not finding it directly :smiley:

Another question - Do I have any opportunity of getting these sums in another Table? Now I used this table to calculate all the holidays and it will become more and more with time and I want these holiday numbers to go into another table, where I calculated the holidays of employees to deduct the days they already used from this number. I hope its understandable what I mean. Otherwise I can post another screenshot also.

Thanks so much :slight_smile:

1 Like

Hey :slight_smile:

So in essence what you would want to do, is reference the sum in another field, in another table?

That is not possible, the sum you see at the bottom can’t be referenced by anything.

Something that might help to understand is, that you need to treat these tables as database tables, not as excel spreadsheets. (if that makes sense)

Maybe there is some funky way to achieve something similar using formulas? Maybe @nigel can help out here and confirm if this is a possibility :slight_smile:

Yeah, I am not really a programmer or anything so I mostly used Excel. Probably I still think in that logic to much. If there is a formular that could help me achieve this, that would be great. I tried some out, but it didn’t work unfortunately. I was thinking maybe with some kind of combination using lookup() but I dont know how exactly.

Thanks for helping guys!

Okay so I looked into this and there is a way to achieve this with a little bit of manual labor.

Here is a demo:
sum-lookup

What you would have to do, is you would need to add a link row field to the table where you stored all the holiday entries. Then you need to add. a lookup field that looks up the number of days that you have entered in that related table.
Then you would need to manually add all the links for a specific employee to the link row field cell, so that you get a list of numbers in your lookup field that would represent each time that person has taken holidays.
Then you can add a formula field that calculate the sum of the list of numbers in the lookup field.

This is very hard to explain in text but I hope the above makes sense to you.

If you have any specific questions feel free to ask, I don’t except anyone to understand what I am talking about :smiley:

Makes Sense. I will try it out and give Feedback if it works, as soon as I come back in baserow :smiley: (Unfortunately didn’t save my password and now I am waiting for the Reset Link)

But this would also mean, that I would have to link new entries every time right? Like there is no automation that would allow me to add days automatically if a new holiday entree is made in the first list, right?

1 Like

Yes that’s exactly right, if you use the solution above you would need to manually add a new entry to your link row field every time a new entry was made in the holiday table.

That can be automated via webhooks, and for a developer implementing smth that does that would be very manageable but you already mentioned that you are non-technical.

If this is something you use for your company and you happen to have a technical person available then that person should be able to setup the webhook correctly :slight_smile:

Otherwise, maybe there is some cool formula trick that I don’t know of that can do this without manual work? I am a little doubtful, but @nigel has built our formula system so if anybody knows, then it’s him :smiley:

Yeah, I will probably manage to find a technical person in our company, that can do that. Our Plan is to automise as much as possible with Baserow, of what we are currently displaying in Excel. So that’s a good hint. I’ll let them know :slight_smile:

It works! Now I am facing the next problem though…

I successfully implemented the Vacation days taken in “Genommener Urlaub”. Now I want to see the remaining vacation in the column “Verbleibender Urlaub”, so I used the formular as seen here:

The formular seems to be right, at least I dont get an error, but the final output is still blank. Is that because I can’t deduct formular fields from each other?

So you probably won’t have to use minus in order to make this work, you can also just use simple operators like + or -.

So you can do something like this:
Screenshot 2022-09-21 at 11.36.37

Hm I tried that aswell, that is my formular:

field(‘Urlaubsanspruch 2022’) - field(‘Genommener Urlaub’)

But the field still remains blank:

Just to make 100% sure, did you press save after editing the formula or did you just click outside of the context to close it?

Oh wow. Sorry :frowning: That seemed to be the mistake haha

Haha no worries, it happens :smiley:

Hey, it 's me again. So I was able to further develop our model. We can now enter Holidays in one table and it automatically transfers them to our holiday calculation model via lookup fields. The fields look like this now:

So: We enter the Holidays in the Table “holiday” Link them to the field Employees, so that every time a new holiday is entered the name and the numbers are automatically transferred to this table.

I also linked the Employee field to our Holiday Calculation to be able to deduct the sum of the days taken from the total holidays available, which are schon in the first field “verbleibender Urlaubsanspruch”:

Now I tried summing these days with two different approaches:

First I tried to Sum the days looking up the values in our employees sheet using:

sum(lookup(‘Mitarbeiter’,‘Urlaub in Tagen’))

Sum(lookup(‘Mitarbeiter’ refers to the employees table because Mitarbeiter is linked to employees and ‘Urlaub in Tagen’ refers to the days counted in this table. However, I get the following error:
Error with formula: argument number 1 given to function sum was of type array but the only usable type for this argument is a list of number values obtained from a lookup or link row field reference.
Is it not possible to sum a field in another table via this function? This would be my favourite option, because then I dont need to create the same lookup fields in the holiday table, which I already created in the employee table just tot sum the days.

Anyways, I also I tried to use: sum(field(‘Urlaub in Tagen’)) to simply sum the lookup field which I already created in this table. However now I get the same error:
Error with formula: argument number 1 given to function sum was of type array but the only usable type for this argument is a list of number values obtained from a lookup or link row field reference.

I dont understand why the sum funtion does not work here. I used it the exact same way before, and I checked the formular 10 times and can’t see what is wrong. Maybe you’ll be able to help me once again :smiley:

Thanks so much!

Hey there :slight_smile:

So I ran this myself and it seems to confirm what you are saying.

In summary: when you sum on a lookup field it works. When you sum on a lookup field that looks up another lookup field, sum does not work anymore.

That’s very interesting.

@nigel do you know if this is a bug?

Hi unfortunately this is a known bug with lookups, a lookup of a lookup can no longer be aggregated and is partially tracked by this issue: Lookup of Lookup fields appear as JSON when used in formulas (#1027) · Issues · Bram Wiepjes / baserow · GitLab

The main workaround currently is in the intermediate table to insert a formula which does the sum in there, and then lookup that formula in the final table and sum it again, if that makes sense? This way you never have a “lookup of a lookup” but in both tables a “lookup of a single value formula”

New Day, New Question :smiley:

I have the following Table:

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.

Thank you so much and kind regards,

Tom

Hey Tom :slight_smile:

This is possible by using filter.

You can do something like this:

sum(filter(field('Times'), field('Billable?') = true))

Or in short:

sum(filter(field('Times'), field('Billable?')))

This basically filters the field Times 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.

1 Like