# Excel - count items from multiple columns **URL:** \ **Category:** Factual Questions\ **Created:** [April 13, 2026, 12:05am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232 "2026-04-13T00:05:55Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 13, 2026, 12:05am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/1 "2026-04-13T00:05:55Z")
My data looks like (yes, I know it is not structured the way Excel wants it) | Australian | French | Wimbledon | US Open | | --- | --- | --- | --- | | Carlos Alcaraz | | | | | Jannik Sinner | Carlos Alcaraz | Jannik Sinner | Carlos Alcaraz | | Jannik Sinner | Carlos Alcaraz | Carlos Alcaraz | Jannik Sinner | | Novac Dokovic | Novac Dokovic | Carlos Alcaraz | Novac Dokovic | | Raphael Nadal | Raphael Nadal | Novac Dokovic | Carlos Alcaraz | | Novac Dokovic | Novac Dokovic | Novac Dokovic | Daniil Medvedev | | Novac Dokovic | Raphael Nadal | [cancelled] | Dominic Thiem | | Novac Dokovic | Raphael Nadal | Novac Dokovic | Raphael Nadal | | Roger Federer | Raphael Nadal | Novac Dokovic | Novac Dokovic | And I want to count all the champions: 22 - Novac Dokovic 20 - Raphael Nadel etc Any ideas? ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [April 13, 2026, 12:12am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/2 "2026-04-13T00:12:41Z")
Still trying to understand the problem … 1. So you first want to identify all the names that appear anywhere in the grid of names, ignoring any duplicates. And present that as a list in a separate section of the sheet. 2. Then you want to count the corresponding number of entries in the grid which match each of the names from step 1. And enter that into your list next to the corresponding name. 3. Then you want to sort the result so the name with the most entries is the first, and the name with the least entries is last. Is that an accurate statement of your goal and one approach to solving it? Said another way, is the goal to “Have a de-duplicated list of names that appear anywhere in the grid, each accompanied by its number of entries in the grid, and with the list of counts + names sorted by number of entries in descending order”? ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [April 13, 2026, 12:19am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/3 "2026-04-13T00:19:33Z")
Count the number in each column, and add those together? ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [April 13, 2026, 12:46am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/4 "2026-04-13T00:46:45Z")
If you’re willing to manually identify all the names, and enter list of names in your results section of the sheet, having Excel generate the counts are trivial. If your grid is cells B2:F27, then `=CountIf(B2:F27, "Carlos Alcaraz")` will result in the number of Carlos entries in your grid. I hope it’s obvious how to generalize that, with a list of names in e.g. column B and a list of `CountIf()` expressions next to them in column A taking their comparison value from Column B of the same row. Getting them into sorted order is where it gets hard using just formulas versus macros or pivot tables. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 13, 2026, 4:35am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/5 "2026-04-13T04:35:02Z")
> [@LSLGuy](#): > > Is that an accurate statement of your goal and one approach to solving it? Yes exactly. And to auto update like a pivot table. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [April 13, 2026, 11:03am UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/6 "2026-04-13T11:03:50Z")
Good. A clear understanding of the goal is a key step to solving anything. I am _not_ a pivot table maven up to this task. But I suspect they are the (only?) way forward if you want the process totally self-updating and automated when existing or new names are added to your grid. This process would be trivial as a macro, but if you’re not already a computer programmer of some sort you’ll never get it working right. It’s not the sort of macro where you can just activate the keystroke/mouse recorder while you do the work once then play it back later when you have new data. It would be real coding. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [April 13, 2026, 12:29pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/7 "2026-04-13T12:29:58Z")
I doubt there is a practical way to do this with a pivot table. There is a simpler way using the approach indicated by @LSLGuy and @Chronos Set out your data as below and then set up a second table under that as shown. The names in ColA you need to add manually The work in the lower table is done using countif formulas. The formula for cell B17 is shown. You can copy that across and down. Put a row sum in ColF and then sort the lower table by ColF descending. You may want to add a total Slams to check that all titles (=33) are accounted for in the lower table. Might even be worth having the names selected from a data validation droplist. You will need to adjust the ranges as further years are added, and similarly any new title holder. [![](https://i.ibb.co/sJgJ2bn6/Excel-Table.jpg) ](https://i.ibb.co/sJgJ2bn6/Excel-Table.jpg) ---
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [April 13, 2026, 12:57pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/8 "2026-04-13T12:57:09Z")
To get the list of unique names, the newer versions of Excel have the Unique function, so: =unique([range of all names]) will return the list Then, in the next column: =countif([range of all names], [cell to the left]) and copy down. Make sure you use $ to specify that the range doesn’t change (if the names are in B2:E10, then it should be $B$2:$E$10 in the second formula). BTW, Claude AI would do this very easily. That is, it would create the spreadsheet for you, with all the formulas. ---
**Author:** ![md-2000](https://avatars.discourse-cdn.com/v4/letter/m/9d8465/32.png) [@md-2000](https://boards.straightdope.com/u/md-2000)\ **Post date:** [April 13, 2026, 1:23pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/9 "2026-04-13T13:23:19Z")
I was trying to do the same and UNIQUE() didn’t work, so I looked it up and apparently Office 2019 is before the UNIQUE function was added to EXCEL. It is in 2021 and later and 365 ---
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [April 13, 2026, 1:46pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/10 "2026-04-13T13:46:00Z")
Yeah, it’s only in later versions. Unique (and other similar functions) are so dang useful! The newest versions have tons of really useful new functionality, especially the way they deal with array formulas. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [April 13, 2026, 2:46pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/11 "2026-04-13T14:46:00Z")
MSFT has decided that since the world uses Excel for everything, it needs to be able to do everything. Database, query tool, statistical tool, presentation tool, word processor, etc. Which also points out that _any_ computer question needs to include which device, OS, & app(s) version(s) the OP is working with. e.g. Excel for Mac 2010 and Excel 365 for PC are two very different beasts. ---
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [April 13, 2026, 2:49pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/12 "2026-04-13T14:49:24Z")
True, Excel 2010 is 16 years old now, so has very different capabilities. More powerful than Multiplan or Visicalc, though! Anyway, if you tell Claude what version you have, it would create a compatible spreadsheet, I’m sure. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 14, 2026, 2:04pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/13 "2026-04-14T14:04:29Z")
OP here. I got it working… sorta. A VSTACK function will turn the four columns into one. Then you can do a conventional Pivot Table to count the winners. For some reason I can’t sort the Count of Winners column in the PT So I use a SORT function to do that. Kinda clunky. ---
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [April 14, 2026, 2:12pm UTC](https://boards.straightdope.com/t/excel-count-items-from-multiple-columns/1029232/14 "2026-04-14T14:12:28Z")
OK. Seems like the other suggestions may have been less clunky.