I want have students submit the same Google Form every time they use their pedometer or Fitbit. Then I want the sheet to sum the students steps each time a form is submitted. I don’t want to manually scan through 500 responses and add each student individually. Any ideas on how to accomplish this? 
-
Welcome. Do you have a test sheet?– marikamitsosCommented Oct 21, 2020 at 1:29
-
I essentially want to add the totals by email address. I tried SUMIF but would have to put all 500 email in. Is there a way around that?– MegTCommented Oct 21, 2020 at 2:13
-
In that case my provided formula meets your needs. All you have to do is adjust the ranges. Can you do that?– marikamitsosCommented Oct 21, 2020 at 2:14
Add a comment
|
1 Answer
Taking into consideration the little info you give, I imagine that if your response sheet (Form Responses) looks something like this:
+---------------------+-----------+------------+---------------------------+-------+
| Timestamp | Last name | First name | Email | Steps |
+---------------------+-----------+------------+---------------------------+-------+
| 2020-10-19 23:25:51 | Boc | Florencia | [email protected] | 356 |
| 2020-10-19 22:33:29 | Boc | Florencia | [email protected] | 175 |
| 2020-10-19 19:58:08 | Splevins | Donny | [email protected] | 153 |
| 2020-10-19 18:40:52 | Castagne | Cedric | [email protected] | 382 |
| 2020-10-19 17:56:40 | Gipp | Eloisa | [email protected] | 147 |
| 2020-10-19 17:26:39 | Splevins | Donny | [email protected] | 365 |
| 2020-10-19 17:09:17 | Riccardi | Monica | [email protected] | 289 |
| 2020-10-19 17:07:08 | Boc | Florencia | [email protected] | 284 |
| 2020-10-19 16:54:45 | Gipp | Eloisa | [email protected] | 174 |
| 2020-10-19 14:20:50 | Riccardi | Monica | [email protected] | 233 |
| 2020-10-19 14:02:00 | Splevins | Donny | [email protected] | 173 |
| 2020-10-19 09:51:38 | Riccardi | Monica | [email protected] | 177 |
| 2020-10-19 04:49:03 | Splevins | Donny | [email protected] | 320 |
+---------------------+-----------+------------+---------------------------+-------+
If so, you then need to create a new sheet and place the following QUERY formula i cell A1
=QUERY('Form Responses'!A1:E,
"select B, C, sum(E) where A is not null group by B, C ",1)
(Please adjust ranges to your needs)
If still in need please let us know.
answered Oct 21, 2020 at 2:07
