I'm not sure if I'm wording this properly.. I have two columns - one with an event name that can contain duplicate values, and another that contains the week number the event occurred. The event can happen multiple times a week in any order or it might not happen at all.

The part I'm stumped on is the formula to take each unique event name and then count how many times the event occurred in a week. Each week representation would be a separate column. The presented data would list the name of the event only once(no certain sorting is required), with the tallies in the proceeding columns. There will hundred of unique event names, so it's not tenable to list them all out by hand, they need to be generated in the presentation sheet programmatically. How can this whole thing be done?

Here's a link to a sample sheet I made, the first page is the raw data, the second is how I want the data to be presented(no formulas were used for the sample). https://docs.google.com/spreadsheets/d/1wWjvAmmAUfwv0yX1amqnKhsrc4hkexRZW6cXQWqDWAk/edit#gid=0

1 Answer 1


enter image description here

A2: =sort(UNIQUE('RAW DATA'!A2:A),1,1)

B2: =COUNTIFS('RAW DATA'!$A$2:$A, $A2, 'RAW DATA'!$B$2:$B, B$1)

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.