How do you write an expression to sum the val...

How do you write an expression to sum the values in a single column?

I created a VC to get the values for each “Yes” in a column, but now I want the total of all the “yes’s” for that column.

Sum([col]) triggers an error.

0 16 1,816
16 REPLIES 16

@Tammi_Canelli SUM(SELECT(TableName[Col],[YesColumnName]=“Yes”))

COUNT would be probably better

You need to create an additional table and calculate values there.

I’m trying that too.

I have added a totals table.

Made sure the column names match the table I needs totals from but not making any headway finding expressions that will work.

The expressions in Appsheet make me want to throw my computer out the window

So let’s say you have a totals table and it has just one row with one actual column “MyTotal”. Now add a virtual column to it and give it the formula you wrote earlier: COUNT(SELECT(Medical Needs Program Registry COMPLETED NORTH [RegID],[O2 under 5 lpm]=“Y”))

it should compute 276 or whatever is the right count. That’s it.

If what you really want is for this total to be computed in a spreadsheet cell, it might be simpler to just use a spreadsheet formula in that cell rather than having the app compute it.

If you need to see the totals in the sheet, how about using a formula in the sheet itself?

If you also want to see totals in the app however, you’d need to create a table as Aleksi suggested. Your Totals col should be a VC so it would update automatically every time the app syncs. Your formula above should work fine here.

Thank you.

I got all totals to work.

I couldn’t do them all in the sheet because I hit the 25,000 cell limit for formulas in Smartsheet.

I did end up having to create virtual columns for a few checkbox fields in order to get the values and then did a sum of all the values in my “totals” table.

Will this create an issue since virtual columns re-calculate more often?

It won’t cause my numbers to double then triple, etc will it?

In general, virtual column computations proceed in a sensible dependency order. You should be able to check to see if they appear correct. They will get recomputed from scratch each time you sync.

This is what I used in the VC: =IF([O2 over 5 lpm],1,0)+IF([O2 under 5 lpm],1,0)

COUNT(SELECT(Medical Needs Program Registry COMPLETED NORTH [RegID],[O2 under 5 lpm]=“Y”))

Totals them but shows by row.

How can I get just a summary row?

@Tammi_Canelli

=SUM ( SELECT (OrderDetails[LineTotal], [OrderID] = [_THISROW].[OrderID]))

This is the expression I use in a VC in Orders Table to give me an [OrderTotal] of

the [LineTotal] from each OrderDetail for that Order.

Something like this should work for you…

@Tammi_Canelli

Well, now that I look at yours better, I think you’re wanting more than what I shared earlier, sorry.

I’ve tried using _thisrow in a few but the test comes up blank.

I just need that 276 number to show in the totals col I created…grrr… so frustrating

I’m afraid you are looking for something that is not possible. You are trying to create a summary row where you can have all calculated or counted values. Am I correct?

Yes.

So there is no way to get column totals?

Sorry i am not following.

what is the first [col]? the key column?

Would I do this in the same table in a VC or can i do it a separate table?

I really want the totals to go to a sheet and i know VC won’t.

Top Labels in this Space