-1

I have the following data in a Google Spreadsheet:

+------+------------+---------------+---------+---------+
| Date | Difficulty |               |Date     | Points  |
+------+------------+---------------+---------+---------+
| Jul15| Easy       | Easy (1pts)   |  Jul15  |4        |
| Jul15| Easy       | Medium (2pts) |  Jul16  |5        |
| Jul15| Medium     | Hard (3pts)   |         |         |
| Jul16| Hard       |               |         |         |
| Jul16| Medium     |               |         |         |
+------+------------+---------------+---------+---------+

I would like to add up all the points for a date in the column date/points where points for a date are next to each other. Points depend on difficulty and are in column 3.

I was able to get the dates in column 4 by using

=UNIQUE(A2:A800)

However, unable to get points 4, 5 etc in column 5. Can someone suggest how it can be done?

2

1 Answer 1

1

Y1:Easy Z1:1
Y2:Medium Z2: 2
...

C1:

=ARRAYFORMULA(QUERY({A:A,IFERROR(VLOOKUP(B:B,Y:Z,2,0),B:B)},"Select Col1,sum(Col2) where Col1 is not null group by Col1",1))
  • VLOOKUP to convert "difficulty" to "points"
  • QUERY to aggregate values
3
  • This is awesome, would have never been able to reach it by me. You are an excel guru for sure. :) Can you pls suggest: 1. How can I change "sum Difficulty" to "points" Thanks
    – CodeMonkey
    Jul 17, 2018 at 18:22
  • Modified it using label, kept trying it with column name "AS"
    – CodeMonkey
    Jul 17, 2018 at 18:31
  • @Code Yes. You can do it with label. Solved?
    – TheMaster
    Jul 17, 2018 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.