I have two tables, one called Objectives which has an “objectives” column, a “goal” column which has a numeric value(i.e. “4” individual sales), and another column called “sum of all progress” which should track the total progress(sum) of all “project input” from the other table.
The other table I have called: “projects”, each project is related to an objective(enum). Each project has a column “input” that shows a numeric value which represents how much will this project adds to the total of the “goal” for that “objective”.
What I need to do is autopopulate the column “sum of all progress”, with the sum of all the projects “inputs” to the “goal” for that specific “objective”
Something like SUM.IF formula for Google Sheets.
I tried using SUM(SELECT) formula but it returns that it cannot compare Lists with texts.
Thanks!
Hope anyone can help. I hope I was clear enough, english is not my native language.
How do I do SUMIF() or SUMIFS()? SUM( SELECT( Orders[Total Amount], AND( ([Customer] = [_THISROW].[Customer]), ([Order Date] >= [_THISROW].[Order Date]) ) ) ) Use SELECT() to gather values from a single column only. See also: AND(), SUM()
An App formula for a normal (not virtual) column is only recomputed when the row is updated by being saved from a form view or modified by an action. A virtual column App formula is recomputed the same as for a normal column, and whenever the app syncs. The value of a virtual column, however, is not saved to your spreadsheet. If you want the value saved to your spreadsheet, use a normal column.
I think I understand, and my previous comment still applies. Because you’re using a normal (not virtual) column, existing rows won’t get a value in that column until the row is saved in a form view. Try opening one of the existing rows in a form and Save (without making any changes, even). I expect the sum will then be computed.
The actual table in Google Sheets is not being updated, neither the deck view for “Objectives”, but when I click to edit an objective in a Form view I can see the value.
Which is what I think you meant here:
" Try opening one of the existing rows in a form and Save (without making any changes, even). I expect the sum will then be computed."
This does work, but I need this to happen when a form is saved in the Projects View. I actually dont want the Objectives to be editable by the user.
This is the formula used for column [Sum of all input for projects] in the Objective table:
SUM(SELECT(Project[Input for Objective], [Objective]=[Objective]))
If you can make this a virtual column instead it would fix your problem but I know that is not always an option. So unfortunately the only way I have found around this when you can’t do a virtual column and you need the column to be updated based on OTHER records being updated is to do it as an excel formula. I know Appsheet frowns on that because it is not as efficient, but sometimes I can’t get around it on some of mine for this very reason. Set it up in excel properly to do what you want with a Vlookup, copy it to the whole column and then refresh your columns in appsheets and it should pick up the equation in the excel equation section of your columns and then delete your current one. If not, then you can copy the excel formula to the appropriate appsheet column, I’m just always afraid I’ll screw it up if I do it that way. Then it will update when the data changes not when you save that record the next time.
