May 24 2023 05:26 PM
May 24 2023 05:26 PM
I have created a MS Form with 35 Yes or No questions. Each Yes question has a certain numerical value that I need to tally and report on.
I did do an IF statement, and it works, but I think it will be an admin nightmare to keep doing this manually when users fill out the Form.
I'm not the smartest with formulas or automation, but am hoping someone can share their wisdom.
May 24 2023 11:50 PM
To automate the tallying and reporting process for your MS Forms responses in Excel, you can use a combination of Excel formulas and functions.
Here is a step-by-step guide to help you achieve this:
=IF(B3="Yes", VLOOKUP(B2, A2:D2, COLUMN(D2)-COLUMN(A2)+1, FALSE), 0)
Replace B3 with the cell reference where the response is located, B2 with the cell reference where the corresponding numerical values are located, and A2:D2 with the range that includes the question labels and numerical values.
This formula uses the VLOOKUP function to find the numerical value for the question and multiplies it by 1 if the response is "Yes" or 0 if the response is "No".
Replace E3:E100 with the range that includes all the calculated values for each response.
This formula adds up all the scores from the responses and provides a total.
By following these steps, you can automate the process of tallying and reporting the numerical values for "Yes" responses in your MS Forms.
The scores will be updated automatically whenever a user fills out the form.
If it is not what is requested, I recommend adding additional information. Information such as Excel version, operating system, storage medium, etc.. A file (without sensitive data) or photo/s with a step-by-step explanation would also be advantageous.
Apart from that, I wish you much success in your project.
May 31 2023 07:37 AM
Hi NikolonoDE! Thank you for this response. I am trying to figure out how it will work. My current situation is:
I've attached some screenshots for reference. Thank you so much!!!!
May 31 2023 08:22 AM
As variant you may to the structured table on first tab which collects form responses few other columns. That won't destroy automatic update of this table.
In additional columns you may do calculations (using structured references) for number of Yes, PASS or not PASS, whatever. That will be updated with each new response added.
On another sheet(s) that could be summary, aggregation, dashboard, what you need to show based on table data.