Finding the count of first appearance and repeat visits

[jarust91]

Hello,

I have a table that contain all of our programs user names and month of use. Throughout the table there are instances where a name is repeated signifying a repeat use of the program. I am trying to find a way to be able to see the total number of "repeat users" in any given month. Additionally, I want to be able to find a way to see the total number of "first time users" in any given month.

I have created a pivot table where the name is the row and the month is the column. I then showed "count of name" in the pivot table. I am not certain if this is the right course of action, or if there is another way to do this. I tried to upload pictures of the pivot table and the regular table, but I can't seem to be able to figure it out.

Thank you for any help that can be provider

 

[NiMip]

Is the table below something like what you're after? Involves adding another column to your dataset, and the second table on the right could be a pivottable as you described in your original post, if you'd prefer.
In my scenario, a person is a first-timer the first time they login, and a repeat for any future logins. If you want to find single-time users it would be a slightly different solution.

[TABLE="class: grid, width: 679"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Login Month[/TD]
[TD]First-timer or Repeat?[/TD]
[TD][/TD]
[TD]Month[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]First-timer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Repeat[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane[/TD]
[TD="align: right"]Jan-15[/TD]
[TD]=IF(COUNTIFS($C$3:$C$17,"<"&C3,$B$3:$B$17,B3)=0,"First-timer","Repeat")[/TD]
[TD][/TD]
[TD="align: right"]Jan-15[/TD]
[TD="align: right"]=COUNTIFS($D$3:$D$17,G$2,$C$3:$C$17,$F3)[/TD]
[TD="align: right"]=COUNTIFS($D$3:$D$17,G$2,$C$3:$C$17,$F3)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jeff[/TD]
[TD="align: right"]Jan-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD="align: right"]Feb-15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John[/TD]
[TD="align: right"]Jan-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD="align: right"]Jan-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD="align: right"]Apr-15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jess[/TD]
[TD="align: right"]Feb-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD="align: right"]Feb-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jerry[/TD]
[TD="align: right"]Feb-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jenny[/TD]
[TD="align: right"]Feb-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill[/TD]
[TD="align: right"]Mar-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jenny[/TD]
[TD="align: right"]Mar-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane[/TD]
[TD="align: right"]Mar-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jess[/TD]
[TD="align: right"]Mar-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jill[/TD]
[TD="align: right"]Apr-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Josh[/TD]
[TD="align: right"]Apr-15[/TD]
[TD]First-timer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jess[/TD]
[TD="align: right"]Apr-15[/TD]
[TD]Repeat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

I may have incorrectly assumed the person only appears once per month. If the dataset has multiple logins for a person in a single month, the solution above won't work so well. Let me know if that's the case and I'll do my best to come up with another way.