Top 10 for the month.

Question

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 · Accepted Answer

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 · Accepted Answer

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