Forum Discussion
Solved
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 ea...
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:
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!
- Very fancy Mr. Guru !
Many thanks for your insights
Scott