Forum Discussion
Can I use wildcards in Structured References to Create A New column summing values
Dear Users, I am trying to create a new field in an Excel data table containing the responses from MS forms survey of child care centers. Since a center can have several facilities (limited to 1...
Formula could be
=SUM( FormTable[@[Current Number of Children:]:[Current Number of Children:9]] )
As for column names I'd don't touch them.
- Hi Sergei,
Thanks for your quick response. I tried to use your codes. It seems it is summing the values of all columns between 1st and the 9th field. However, since the 9 fields for the number of children in my MS forms table are not grouped together but with other fields such as "number of caregivers", the total sum include the values of all other fields in between. Probably I can reorder these fields and put them together?Most probably you may reorder columns. However, I didn't touch forms for a long while and not 100% sure now. Better to test on copy of your form/file.
I guess you are on Excel 365, another variant could be use in total column something like
=SUM( FILTER( MyForm[@[Co1]:[ColN]], LEFT( MyForm[[#Headers],[Col1]:[ColN]], 27 ) = "Current Number of Children:") ) )Even more better to keep generated by Form table as it is and do calculations in separate sheet using FILTER() on table or like; or in separate file with Power Query.
It's always good practice to separate raw data, preparations and reporting.
- Thanks for your reply. It is very informative.
