How to count the amount of times a field appears in a specific 'week' in excel

Question

I have a table:

I've created a new column called Distinct Delivery Week, where I have the weeks 1-52 listed. I want to create a count function if Week == distinct delivery week, make it so it does a count and returns the Channel category that has the most appearances in each respective week.

For example, if App - Activation Organic appears more often than anything else in Week 1, then it will be returned in a new column next to Distinct Delivery Week

Answer 1

With so many data, it may be helpful if you use a simple PivotTable to count how many times per week does each channel appears, and get TOP 1 per week.

I made a fake dataset kind of like yours:

Then I created a Pivot Table like this:

1. Fields WEEK and CHANNEL into rows section (first WEEK, second CHANNEL, the order is important)
2. Again, field CHANNEL into VALUES section (make sure the field does a COUNT operation)
3. Applied a VALUE FILTER in column CHANNEL --> TOP 10, set it up to show just TOP 1

It's a really easy way to get a list where you can see the channel that appears most per week.

Answer 2

I figured out how to do it using this source. The formula I used was:

{=INDEX(D1:D74686, MODE(IF(H2:H74686=M2,MATCH(D2:D74686,D2:D74686,0))))}