Forum Discussion

Copper Contributor

Jun 20, 2024

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...

Formulas and Functions

PeterBartholomew1
Jun 23, 2024
Officeuser7777
It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.
fruit = UNIQUE(TOCOL(Tabelle1,1)) count = COUNTIFS(Tabelle1, fruit)
Counting within 3 columns.xlsx27 KB

Copper Contributor

Jun 23, 2024

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
Platinum Contributor
Jun 23, 2024
Officeuser7777
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.
- Officeuser7777
  Copper Contributor
  Jun 23, 2024
  Thanks - 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.
  - rachel
    Steel Contributor
    Jun 23, 2024
    Officeuser7777
    
    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.
    
    officeuser7777.xlsx10 KB