Search code examples
google-sheetsgoogle-sheets-formula

What is the best way in Google Sheet to Count Unique, and summarize a value on the same row?

I am currently making a spreadsheet where people type in their name, and right next to it, a Value. This will then be sent over to another sheet that will Sort it,then summarize, and sort by name on how much they have earned. For Example:

Sheet 1

Column A Column B
John 2
Dave 1
John 1
Chloe 5
Dave 1

Sheet 2

Column A Column B
Chloe 5
John 3
Dave 2

Also, given the sum on Column B on Sheet 2, what is the best way to give someone a title based of their points? Lets say If you have 5+ points, you get Rank 3, 4-3 points are Rank 2, and below are Rank 1?

Column A Column B Column C
Rank 3 Chloe 5
Rank 2 John 3
Rank 1 Dave 2

I tried to do a mix with =UNIQUE and =SUMIF but it does not seem to work. Anyone have a good idea?

Solution

  • You may try:

    =sort(map(unique(tocol(A2:A;1));lambda(Σ;let(Λ;sumif(A:A;Σ;B:B);
      {ifs(Λ>4;"Big";Λ>2;"Medium";1;"Small")\Σ\Λ})));3;)

    enter image description here