Hello,
I have a table with two columns and many rows, where the first column has some repeated items and the second columns have numbers. Let's say:
I need to obtain a unique list (top10 only) from column A where the sum of column B is above 0. I managed to extract the full list, but I'm having issues to filter the "0". I'm using the following:
=LET(y,ColumnA,x,UNIQUE( y),SORTBY(x,SUMIF(y,x,ColumnB),-1))
Two problems:
I have a table with two columns and many rows, where the first column has some repeated items and the second columns have numbers. Let's say:
ColumnA | ColumnB |
---|---|
D | 3 |
A | 1 |
B | 2 |
C | 0 |
A | 4 |
B | 5 |
C | 0 |
I need to obtain a unique list (top10 only) from column A where the sum of column B is above 0. I managed to extract the full list, but I'm having issues to filter the "0". I'm using the following:
=LET(y,ColumnA,x,UNIQUE( y),SORTBY(x,SUMIF(y,x,ColumnB),-1))
Two problems:
- If the ColumnA has less than 10 unique items, the formula returns the list of items, and then a row with a "0".
- I only need the top10 items where the sum is above 0. I tried to filter this out by using "INDEX(UNIQUE(same formulas as above),SEQUENCE(10))", which works for the top10, but I can't filter out the 0.
Last edited: