Forum Discussion
Listing and counting unique values that match criteria
You have a few options here. I'd go with GROUPBY because it will allow you to apply multiple functions to the data. You may have to change the arrangement, but it handles this task well.
=LET( row_field, Tasks[Wave '#], f, row_field = rpt_filter, values, HSTACK(Tasks[Task '#], Tasks[Task '#], Tasks[Task Status]), fn, HSTACK(COUNTA, ARRAYTOTEXT, ARRAYTOTEXT), agg, GROUPBY(row_field, values, fn, , 0, , f), DROP(agg, 1) )
#1 When I click into the formula it stops working
What does this actually mean/What happens?
#2 and honestly my excel knowledge is too limited to figure out how to duplicate this
- Fair enough. Looking at your previous posts you seem to run Excel 365. Please confirm or tell us which version you run + Windows or Mac?
- Assuming you run 365, if in a blank sheet you start typing: =gr do you see?:
the problem might be that you don't have the GROUPBY function (365 only and not Generally Available yet)
- 365 and no GROUPBY function.
See attached workbook
Note that I modified a value in sheet 'All Task' cell E21 for testing purpose
- Looking at this, this is definitely closer to what I was looking for. The only additional change I need is for the A1 column to auto pull all unique wave values from the "All Task" tab.
This is what I tried, but I just get a "Spill" error even though the rest of the cells are empty.
=UNIQUE(FILTER('All Task'!B2:B2000,('All Task'!B2:B2000<>"")*('All Task'!D2:D2000>0)))
I think GROUPBY and PIVOTBY have just completed beta testing and should available on updated versions of 365.
GROUPBY and PIVOTBY are gradually being released to the Current Channel. Some users already have it, others should get it soon. While the official release was in the 2nd week of September of 2024, I got it on my desktop in the 3rd week, and on my laptop in the 4th week.
