Forum Discussion
Counting within 3 columns
It may already have been suggested but COUNTIFS works perfectly well with 2D Ranges.
fruit = UNIQUE(TOCOL(Tabelle1,1)) count = COUNTIFS(Tabelle1, fruit)
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.
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.
- 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.
=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".
