Counting within 3 columns

[Officeuser7777]

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. 

[#2]

@Officeuser7777 Not a very fancy solution but it could similar to the one in the attached file.

[#3]

Sorry I just see "#NAME?" in the result cells

[#4]

@Officeuser7777 

Which Excel version are you using?

[#5]

Mac Office365

[#6]

@Officeuser7777 Perhaps Power Query is something you would want to consider. Especially when your real data is more complicated than just Apples and Bananas 🙂

[#7]

Thanks, I looked into it, but in another thread I got two very useful suggestions for counting across 3 columns that worked great using SUMPRODUCT. That was even more complicated than this task, so I'm hoping someone can suggest the syntax for a formula like that.

[#8]

Bumped. Still hoping there is a formula solution for this. I tried messing with Power Query but did not figure out a fix.

[#9]

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

[#10]

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.

[#11]

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

[#12]

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

[#13]

@Officeuser7777 

=HSTACK(UNIQUE(TOCOL(DataTable,1)),BYROW(UNIQUE(TOCOL(DataTable,1)),LAMBDA(r,SUM(N(DataTable=r)))))

The attached sample file should work if i correctly understand what you want to do. The formula dynamically updates the results when data is entered in the dynamic table "DataTable".

[#14 · Accepted solution]

@Officeuser7777 

It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.

fruit
= UNIQUE(TOCOL(Tabelle1,1))

count
= COUNTIFS(Tabelle1, fruit)

 

[#15]

Peter and Oliver, thank you. That worked great. Wish I could tag you both with best answer.