Forum Discussion
Officeuser7777
Jun 20, 2024Copper Contributor
Counting within 3 columns
Hi, not feeling too smart here. How do I code Excel to make the result table below, counting number of unique occurrences of fruit types across 3 columns? Col1 etc refer to Table columns in DataTable...
- Jun 23, 2024
It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.
fruit = UNIQUE(TOCOL(Tabelle1,1)) count = COUNTIFS(Tabelle1, fruit)
Officeuser7777
Copper Contributor
Bumped. Still hoping there is a formula solution for this. I tried messing with Power Query but did not figure out a fix.
Riny_van_Eekelen
Jun 23, 2024Platinum Contributor
Not sure what got you 'bumped' or what SUMPRODUCT solution you saw. Perhaps the picture below will help, though you should be able use my initial formula suggestion with UNIQUE, TOCOL and COUNTIF in Excel for MS635.
- Officeuser7777Jun 23, 2024Copper ContributorThanks - I bumped this because I am still stumped on how to generate the list in A8..A10 and then count the number of instances of each item in that list across the 3 columns. Sorry, I have long years with Excel, but never got far beyond SUM.
- rachelJun 23, 2024Steel Contributor
Are you sure you are on Mac and your excel is 365? If it is indeed 365 for Mac, then it is impossible that you cannot use UNIQUE or TOCOL.
Without UNIQUE or TOCOL, you need to do something like below. (it is not something that can be solved by SUMPRODUCT).
Feel free to complain about your excel 365 or mac to the relevant custom support if you find the attached legacy solution too painful.
- Officeuser7777Jun 23, 2024Copper ContributorThanks Rachel, to be clear, yes I am on Mac O365, I didn't say that I could not use UNIQUE or TOCOL, and I did not say that I want to use SUMPRODUCT. I am just looking for the syntax to use an appropriate formula. The thread is getting a little tangled. To keep it simple: I am just looking for method to use some formula(s) to get the output we need. Thanks again.