Forum Discussion
Summarize Counts in an Adjoining Column - Excel
Hello,
Thank you if you're reading this. I have a number of users in a column (array) called EMail. Sometimes they are in the column multiple times. I need to count the total occurrences for each user and put it in the adjoining column.
As an example, in the data below, Deb Ruiz appears 5 times. Megan strand occurs 4 times. I need to keep the Email (User) and the count associated with the manager's name to the right. Ultimately, I will email the manager and tell them that "this happened 5 times with user Deb Ruiz" and "this happened 4 times with Megan Strand etc... I have about 700 total user instances in the Email row / array. What formula will work?
Can anyone help me please?
Scott
| Count | Manager Name | |
| Deb Ruiz @ email | Alison Travis | |
| Deb Ruiz @ email | Alison Travis | |
| Deb Ruiz @ email | Alison Travis | |
| Deb Ruiz @ email | Alison Travis | |
| Deb Ruiz @ email | 5 | Alison Travis |
| Megan Strand @ email | Alison Travis | |
| Megan Strand @ email | Alison Travis | |
| Megan Strand @ email | 4 | Alison Travis |
| Megan Strand @ email | Alison Travis | |
| Josh Allen @ email | 2 | Alison Travis |
| Josh Allen @ email | Alison Travis | |
| Lilly White @ email | 1 | Andrea Villarreal |
| George Jetson @ email | Andrea Villarreal | |
| George Jetson @ email | 4 | Andrea Villarreal |
| George Jetson @ email | Andrea Villarreal | |
| George Jetson @ email | Andrea Villarreal |
Easy with a pivot table.
Select any cell within your table and choose Insert, Pivot table
Drag both Email and Manager name fields to the row area. The drag either of the two to the bottom-right area. See screen-shot:
7 Replies
Something similar with a DA formula and a bit of conditional formatting
= LET( distinctPairing, UNIQUE(Table1), distinctEMail, TAKE(distinctPairing,,1), distinctManager, TAKE(distinctPairing,,-1), occurences, COUNTIFS(Table1[Email],distinctEMail), resultList, HSTACK(distinctManager, TEXTBEFORE(distinctEMail,"@"), occurences), SORT(resultList) )Hi Peter ! Wow, you really have a solid grasp of Excel formula concepts. Thank you very much for your time in presenting this solution. Ever appreciated. I may just give it a gander.
Respectfully, Scott
If you have access to a 365 license this should at least prove the spreadsheet is real and not an image drawn from a fevered imagination!
Easy with a pivot table.
Select any cell within your table and choose Insert, Pivot table
Drag both Email and Manager name fields to the row area. The drag either of the two to the bottom-right area. See screen-shot:Jan - thank you so much for the reply..... I did these steps as that is all that I could see to do....
Almost works but I am not getting the Count it appears >>
Just below Column heading "Count" I chose Insert and pivot table
Selected Table / Range is --- '90 Day all_phishing_activity_08'!$A$1:$I$656
Choose where you want the Pivot Table Report to be placed - Chose Existing Worksheet----- '90 Day all_phishing_activity_08'!$B$4- Actually I think I got it.... I had to drag the Count field down to the Values Box
THANKS JAN !!
PS - Any relation to Sasha Pietrse ?
