Event Registration with Form & Summary

Jennifer Dewhurst

I am using the Event Registration template with Web Form template to register people for a number of volunteer events. I am using a SUMIF formula to count the number of people registered for each event. My problem is this: When I use a range of cells to indicate which cells should be counted, the Web Form puts the next entry in the row AFTER the reference in the formula. If I indicate the rows to be counted are only the populated ones, the Summary doesn't work. It doesn't make sense to me because this is how the template is supposed to function. 

For instance: If I indicate the entries to be counted are B22: B62, Smartsheet puts the next entry at B63 even though all the other rows are blank. If I indicate the rows to be counted are B22:B25 (because they are populated) Smartsheet correctly puts the next entry at B26 but the Summary does not work. (see screenshot) . What am I doing wrong?

Any help is greatly appreciated. 

Jennifer Dewhurst

My formula for each event is this: =COUNTIF(Event22:Event65, Event7). With this formula, each new entry via the Web Form goes to Row 66 and after.

If I use this formula: =COUNTIF(Event22:Event25, Event7), the summary doesn't count the new rows. 

Jennifer Dewhurst

Would it be better to use a regular registration sheet and create a report for how many people are registered for each event?   I can do this but it seems to me the template is designed to summarize the children rows.

KrisWalsh

Hi Jennifer,

The following will make your formula easier. 

1. Shift the data in rows 1-7 one column to the right. (Copy/Paste)
2. Change your formulas to look in the entire column. (see example sheet)

Greater Detroit Event Volunteers 

I also added in some other features like 'Duplicate Volunteer' and 'Volunteer Goal Met'.

If you want to make a copy of it, send me your email address & I'll share it to you. (:

wkris@vmware.com