Forum Discussion
Solved
Listing and counting unique values that match criteria
I want to create a list off another list but i'm having some issues getting things the way I want. The "All Task" is the list i'm pulling data from. I want to grab each unique Wave Number and th...
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) )
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)))
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 formatted all as Tables. Dynamic Arrays don't spill in Tables => #SPILL!
So, in the 'Task Search' sheet select any cell in green > Go to Table Design (tab) > Convert to Range > Yes
COUNT only looks at numeric values.
Your wave numbers are text, however, even though they look like numbers.
Use COUNTA instead of COUNT
- Gotcha, did this and then Columns C is now showing Zero's and D-F are now blank.
