Dec 06 2021 11:05 AM
Dec 06 2021 11:05 AM
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.
Dec 06 2021 11:35 AM - edited Dec 06 2021 11:36 AM
Formula could be
=SUM( FormTable[@[Current Number of Children:]:[Current Number of Children:9]] )
As for column names I'd don't touch them.
Dec 06 2021 12:44 PM
Dec 06 2021 03:08 PM
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.