Can I use wildcards in Structured References to Create A New column summing values

[Lijun_Chen]

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 10), the table has several fields indicating the number of children in each facility. The filed names for these fields are the same "Current Number of Children: 2/3/4/.../10" except for the index numbers 2 to 10 at the end, which is automatically added by MS Forms to indicate whether the question is the 2nd or 3rd. 

I want to use Structured References to these fields to create a column field showing the total number of children in all the facilities of a center. I am using the Sum() function to reference all the 9 fields to get the summed value of all the fields.

=SUM([@[Current Number of Children:]],[@[Current Number of Children:2]],[@[Current Number of Children:3]],[@[Current Number of Children:4]],[@[Current Number of Children:5]],[@[Current Number of Children:6]],[@[Current Number of Children:7]],[@[Current Number of Children:8]],[@[Current Number of Children:9]])

Since the field names are the same in the formulae, I wonder whether there is any wildcard function to reference all the fieldnames instead of listing each of the field names?

My 2nd question is whether I can rename the field names in the Excel table to short ones? Will the MS Forms survey still recognize these fields and record the responses data correctly? 

Thanks a lot for your help.

LC

[#2]

@Lijun_Chen 

Formula could be

=SUM( FormTable[@[Current Number of Children:]:[Current Number of Children:9]] )

As for column names I'd don't touch them.

[#3]

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?

[#4]

@Lijun_Chen 

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.

[#5]

Thanks for your reply. It is very informative.