COUNTER.IF ASSISTANCE

[EVG08]

Hello, I need some assitance.

This is tha data I'm working on (MAIN TABLE):

NOTE: In this dataset all the letters that aren't in caps are referred as teams for the purpose of the project.

The main objective of this dataset is to show how many points each team accumulated each week, the values in bronze, silver and gold are the TOP 3 teams of each week.

When I use the counter.if function to count the number of max value ocurrences, it works when all the max values are in column R (team l) with a value of 12.00 for a total count of 13 as you can see in the next table:

But when let's say one of the max values is somewhere in column J (team d):

This is the counter table result:

As you can see, it counts the max value in column J (team d), but does not include the other max values that remain in column R (team l), the excpected result is 1 time for team d and 12 times for team l, but I get what you see in the picture just above.

This is the function used in the counter table for the "1st" column:

NOTE I: my microsoft is in spanish and that's why you see "CONTAR.SI" instead of "COUNTER.IF".

NOTE II: I just showed 2 functions, but every team has a counter function (see that the ranges in blue are different in the pictures above).

How can I fix this problem so the counter table can count and display the max values when they are scattered across the MAIN TABLE?

I apologize if anyone gets confused with the explanation.

[#2]

@EVG08 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@Hans Vogelaar 

Hi, here is a link to the google drive with the file:

https://docs.google.com/spreadsheets/d/1YrhxbEo76fHzL2q7S-ABHRYu57ipWd5J/edit?usp=sharing&ouid=10698...

In the next picture you can see the 2 tables to work with (team points & weekly pot winner)

Team points is where you input the data and the weekly pot winner is where the counter of max values is set in each cell that has a 0.

By default all the max values (cells in gold) are assigned to team l, you can try giving the max value to other team and check the weekly pot winner table to notice that the counter is not counting all the max values that are in the whole team points table.

Here is a small example with scattered max values (I just typed random numbers higher than 12):

Here the weekly pot winner table should display that:

-team d had the max value 1 time.

-team c had the max value 1 time.

-team f had the max value 1 time.

-team l had the max value 10 times.

But as you can see, it only shows when team f has the max value.

[#4 · Accepted solution]

@EVG08 

Thanks! See the attached version.

I added three helper columns that calculate the top 3 of each row.

[#5]

I was really stressed thinking in a way to solve that problem. Thank you for your assistance!