Count date range based on criteria

Question

Let´s suppose I have generated a report with dates (day/month/year) when soccer teams won titles. This is how the report is going to look like:

Area	Team	Champions League	Europe League
England	Chelsea	27/01/2021	15/01/2021
Spain	Real Madri	27/02/2021	20/01/2021
Spain	Barcelona		18/02/2021
France	PSG	27/03/2021	27/02/2021

My objective here is going to count how many titles each area won per month. So, this is how my desired output looks like:

Area	January	February	March
England	2
Spain	1	2
France		1	1

What I tried to do was the following (for January and England):

=COUNTIFS(Table[[#All],[Champions League]:[Europe League]],">01/01/2021",Table[[#All],[Champions League]:[Europe League]],"<31/01/2022",Table[[#All],[Area]],"=England")

However, my output using this formula is "#VALUE!". Can you please help trying to figure out what I am doing wrong?

Answer 1

COUNTIFS doesn't like your dissimilar sized criteria ranges.

You would be better served setting your data up in a tabular format like so:

Area	Team	Championship	Date
England	Chelsea	Champions League	1/27/2021
England	Chelsea	Europe League	1/15/2021
Spain	Real Madri	Champions League	2/27/2021
Spain	Real Madri	Europe League	1/20/2021
Spain	Barcelona	Europe League	2/18/2021
France	PSG	Champions League	3/27/2021
France	PSG	Europe League	2/27/2021

You get your data into this format easily by using Power Query. Simply load the data in, select the two date columns, and Unpivot.

And then you could use a simple Pivot Table to display the data in your preferred format:

To turn the full date to just the Month name select one cell > right-click > Group > by Month