SUM.IF workaround

Hi everyone,

Im working on a project tracking app demo.

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.

Solved Solved
0 9 1,044
1 ACCEPTED SOLUTION

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.

View solution in original post

9 REPLIES 9

Steve
Participant V

Thanks @Steve! Did this work for the formula.
Im getting the correct results on the Test:

Although the column is not being shown nor the table is being updated…

This is the column config.

Any ideas on what could be wrong?

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.

Thanks @Steve, I am using a normal column.

Maybe I didn´t explain right.

What I need to do is autopopulate the “Sum of all the inputs of projects” that is referenced to each Objective.

Objective Table:
3X_8_1_81dbfafa461cd785ff50952e7cf71921a7b486ba.png

Project table:
3X_9_3_931f1dfc4b8dc1273f6df4583234e8aeb72af76c.png

i.e. In the Objectives Table, The Sum of all the inputs of projects for Objective “A” should be 40.
This is where Im using the SUM(SELECT) FORMULA.

I hope this makes it easier.

Thanks so much for your help and patience.

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.

Im sorry Steve, I must be doing something wrong.

This is what I get:

3X_3_5_356d140259ca6449ffb13130d243b6cd9753a2da.gif

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]))

Any ideas?

Please contact support@appsheet.com for help with this.

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.

Thanks @LongingToProgram !

Virtual column did work.
Also setting up the formula directly in my Google Sheets file.
Didn´t find it necessary to copy the formula into AppSheet.

I think I can find a way to work around with this…

Thanks so much!

Top Labels in this Space