Need to sum column by unique values in another column, and result put into one cell

Currently working on a project, using it as a learning tool. I’ve been working at this problem for hours for what seems to be a simple thing but cant get past

Working with data in A2:E with a header in row 1

I need to add column C based on the uniqueness of column D and column A filtered by the value in cell H2 and the results put into one cell (I2)

My current formula in I2 is

=QUERY(FILTER(C2:D,B2:B=H2),"select Col2, count(Col2) where Col2 is not null group by Col2 label count(Col2) ''",0)

that gets me this results:

| c - Cup   | 1 |
| w - Whole | 1 |

the result I want is simple

| c - Cup 1, w - Whole 1 |

here is the spreadsheet:

I’ve been through numerus lines of formulas. From query to simple filters. to many to list. I’ve been at this for hours

>Solution :

Added a solution here for you to test out:

=let(Δ,query(filter({map(C:C,lambda(Σ,let(Λ,split(Σ,"/"),iferror(round(index(Λ,,1)/index(Λ,,2),2),--Σ)))),D:D},B:B=G2),"Select sum(Col1),Col2 group by Col2 label sum(Col1)''"),
     join(", ",byrow(Δ,lambda(z,join(" ",z)))))

enter image description here

Leave a Reply