Top 10 for the month.

James Sawyer

I have a sheet with a count formula and rank formula.

I also have a report from that sheet that give me the top 10

I also have a dashboard metric from the report that visualizes the top 10 in a graph.

I want to show only the current month in the graph, how do I do this? Do I need to create 12 different reports, one for each month, then change the source metric on my dashboard each month? Is there an easier way?

Jonathan Denis

Hi James,

I don't know exactly how your datas are saved in your sheets, but when I faced a similar problem, I created an helper Checkbox column that was check when the date was within the actual month.

I use this formula in a column format as checkbox.

=IF(AND(MONTH(*****DATE*****) = MONTH(TODAY()); YEAR(*****DATE*****) = YEAR(TODAY())); 1)

Simply replace the " *****DATE***** " with your Data's date.

This equation does validate the acual year so that next year you don't see last year's month in your dashboard.

You then need to filter-out the unchecked box from you reports.

Hope it helps!

Jonathan D.

Jonathan Denis

Hi James,

It still difficult for me to understand, but your function seems OK.

The COUNTIFS() will count only if both conditions are met, are you sure that both conditions are met in your datas?

Bur from the way I understand, the "month helper" column should only be use as a filter in you report to remove datas that are not within the actual month (unchecked). It should not replace/modify your count function that was working prior to this addition…

Jonathan Denis

Jonathan Denis

Hi James,

I don't know exactly how your datas are saved in your sheets, but when I faced a similar problem, I created an helper Checkbox column that was check when the date was within the actual month.

I use this formula in a column format as checkbox.

=IF(AND(MONTH(*****DATE*****) = MONTH(TODAY()); YEAR(*****DATE*****) = YEAR(TODAY())); 1)

Simply replace the " *****DATE***** " with your Data's date.

This equation does validate the acual year so that next year you don't see last year's month in your dashboard.

You then need to filter-out the unchecked box from you reports.

Hope it helps!

Jonathan D.

James Sawyer

@Jonathan D - Thank you, I was able to set up my checkbox, which is the 13th column called "month helper". Now on a separate sheet I'm I'm trying to count. This is what I have so far =COUNTIFS({New FTO and FTC Range 1}, Property@row, {New FTO and FTC Range 13}, true).

The first criteria works fine, but the second criteria (counting the checked boxes is not. What am I missing?

Jonathan Denis

Hi James,

It still difficult for me to understand, but your function seems OK.

The COUNTIFS() will count only if both conditions are met, are you sure that both conditions are met in your datas?

Bur from the way I understand, the "month helper" column should only be use as a filter in you report to remove datas that are not within the actual month (unchecked). It should not replace/modify your count function that was working prior to this addition…

Jonathan Denis