Forum Discussion
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 in the Data sheet. So I presume the array to count in is Data!DataTable[[Col1]:[Col3]] or something like that? And not sure what function to use. Thank you.
It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.
fruit = UNIQUE(TOCOL(Tabelle1,1)) count = COUNTIFS(Tabelle1, fruit)
It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.
fruit = UNIQUE(TOCOL(Tabelle1,1)) count = COUNTIFS(Tabelle1, fruit)Peter and Oliver, thank you. That worked great. Wish I could tag you both with best answer.
Officeuser7777 Not a very fancy solution but it could similar to the one in the attached file.
- Sorry I just see "#NAME?" in the result cells
Which Excel version are you using?
- Bumped. Still hoping there is a formula solution for this. I tried messing with Power Query but did not figure out a fix.
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.
- 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.
