Feedback Submitter Count

calvarado

I have created a Feedback intake form and a Dashboard. In the Dashboard I would like to chart  total count of submissions by each submitter name.  I do not have a list of those who will submit but I know they will submit multiple entries.  So I want to be able to count each of their respective entries.  Lets say John submits 20 entries, Paul submits 10, etc... and graph that by their name. Again the problem is I don't have the names until they submit feedback and it populates my sheet.  In addition, I would like to be able to capture this over the lifetime and monthly changes.  Future changes would be, due to I am expecting a large number of unique submissions, Top 10 Submitters.  Again, that would be by name so it would apply here as well.

Gbryan

I have the exact same question except I need to track request submitted by customer name.

Paul Newcome

The simplest way would be to add a column (we'll call it "Count" for this example). In that column you would put:

 

=COUNTIFS([Name Column]:[Name Column], [Name Column]@row)

 

This will look at the name in that row and count how many time it finds it in the name column.

 

You can then sort your sheet so that the rows with the highest count (most submissions) are at the top.

Paul Newcome

Another option would be to build off of the Count. Add 2 more columns. "Row ID" and "First Entry".

 

For this I am going to assume the name column is titled "Name".

 

The Row ID column I just made as an auto-number type.

 

The First Entry column will be a checkbox type column. Enter this:

 

=IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], Count:Count, >1, Name:Name, Name@row)), 1)

 

This will look at all of your rows. It will collect all of the Row ID's that have the same name as what is in the row of your formula if the Count is above one. It will then check the box if the Row ID for the row that the formula is in is the lowest Row ID number. That means that the box will be checked only if the row meets 2 criteria.

 

1. It has to be a duplicate.

2. It has to be the first entry of that duplicate.

 

You can then pull a report based off of the First Entry checkbox being checked. The report will be the names of all duplicates, but each name will only be listed once (thus the reason for the First Entry column). You can sort the report in descending order which will put your highest count up top.

 

The report will automatically update as new entries are added to the sheet, so once you have it set up, all you have to do is look at the report for a live listing, automatically sorted, of your top submitters.

 

I haven't tried looking at how to keep an auto-updating list of the top 10 yet, but I will if I get a chance today. If not... I'm on vacation until Tuesday. Hopefully someone else will be able to help you further. Otherwise I will approach it on Tuesday.

 

Hope this at least helps get you started.

 

J. Craig Williams

Thanks Paul. This helped me solve a different problem.

Have you tested how row deletion or sorting affects the solution?

Since you are searching the list based on the list contents, I don't think you need the

COUNT:COUNT, >1,

range and criteria. In fact, it will skip the submitters with only one submission.

Craig

Paul Newcome

Happy to have helped Craig! It's not often that happens. Haha. Usually it's the other way around.

 

I have tested with deletion and sorting with no issues at all.

 

I did take out the Count:Count, >1, and it started counting all of the people that only had 1 submission. I figured making part of the criteria as needing to be a duplicate  (Count > 1) will help filter out all of those single submissions from the report since part of what is needed is the ability to evaluate "a large number of unique submissions" and only pull the "Top 10".

 

 

Calvarado: I almost forgot to mention... If you put the formulas in the top row and drag-fill on down to the bottom of the sheet to cover what is already there, the formulas will auto-populate with each new row added even if it is added by a form submission.

J. Craig Williams

Ok on the testing. I didn't, that's why I asked. I'm wary of row ID's -- sure would be nice to have that as feature like @cell.

Check out LARGE

https://help.smartsheet.com/function/large

It could be that the first 8 have duplicates but the others are singles (for example)

Craig

calvarado

Paul great info thank you. I am almost there but still having an issue.  When I keep Count:Count in the formula it give me the "unparseable" error, when I remove it it does what you state in response to Craig where it just counts every duplicate as 1.  My Count column name is actually "Submitter Count" but I have tried with both Count and Submitter Count in the title and in the formula.

=IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], Count:Count, >1, [Submitter Name]:[Submitter Name], [Submitter Name]@row)), 1)

J. Craig Williams

If you have a different column name, then it might like this:

....,  [Submitter Count]:[Submitter Count], >1, ....

Craig

calvarado

Paul, Craig thank you for the guidance.  I was able to make this work, not with the check marks (for some reason I couldnt get them to auto check) but I was still able to use what you provided the results I was looking for.

J. Craig Williams

Good news.

Craig

Paul Newcome

I am normally pretty wary of row ID's as well, but since it is being used as part of a COLLECT along with the name to identify just one of multiple occurrences, it is pretty flexible. It just gives the MIN something to look for. 

 

I also like LARGE for this case. I personally haven't used it yet, but it seems like it would fit pretty well for this.

Paul Newcome

Good deal. 

 

If you are wanting the auto-check for the check marks, maybe post the formula and the actual column names you are using.