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?

CodePudding user response：

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

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

CodePudding user response：

You may try something like this as shown in image below, so I have used the incredibly versatile SUMPRODUCT Function to achieve the expected output

Formula used in cell B8 Fill Down & Fill Right

=SUMPRODUCT(($A8=$A$2:$A$5)*(B$7=TEXT($C$2:$D$5,"mmmm"))*($C$2:$D$5<>""))

To hide the zeros, you may custom format the cells by pressing CTRL 1 --> Format Cells --> Number Tab --> Category --> Custom --> Type --> 0;;

EDIT

Approach Using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table39"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}, {"Team", type text}, {"Champions League", type date}, {"Europe League", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Area", "Team"}, "Attribute", "Value"),
    #"Extracted Month Name" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Area"}, {{"All", each _, type table [Area=nullable text, Team=nullable text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Value", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"All"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Index", List.NonNullCount)
in
    #"Pivoted Column"

Using Power Query will be dynamic, one time operation and every time add new data to the original source the imported table from Power Query gets updated within few by a single refresh, please follow the steps

• First select any cell within the range,

• Then from Data Tab under Get & Transform Data, Click From Table/Range,

• A pop up shall appear, check mark the My table has headers and press Ok

• Data gets loaded into Power Query Editor

• Change the name of the table from Query Settings in the right hand under Properties with CountTbl

• Now select any column and press CTRL A

• If the Changed Type Step is already showing in Applied steps then you don't have to follow this, please goto the next step

From Transform Tab --> Click Detect Data Type

• Now you will find the dates are still showing as Time Stamps therefore select both Champions League & Europa League Columns and from Transform Tab --> Click Data Type from Any to Date only, you shall get a message asking whether to Replace the current step or to add new step, you need to select the Replace

• Well data types changed, now select the Area and Team Columns and press Right Click Unpivot Other Columns also you may find the option from Transform Tab --> Unpivot Columns dropdown and select Unpivot Other Columns

• Next, select the Value column showing dates --> Goto Transform Tab --> From Date & Time Column Group --> Click Date --> Month --> Name Of Month

• Again select the Area Column --> From Transform Tab --> Group By --> Give any fancy name to the New Column Name --> Operation --> All Rows and press OK

• Now goto Add Column Tab --> Click Custom Column and enter this following Formula or the M-Code in custom column formula space

Table.AddIndexColumn([All],"Index",1,1)

press Ok

• So from the custom column newly created, click on the dropdown, and uncheck the box use original column name as prefix and select the columns Value and Index and press Ok

• Remove the column which we created by Group i.e. All --> Select the column and press delete key from your keyboard

• Now select the value column which will be showing the month name and goto Transform Tab and click Pivot Column

• There Values Column needs to be Index and in Advanced Options you to select Count (Not Blank) and press OK

• You shall see the expected output has been achieved!!!

• From Home Tab Click Close & Load To --> Import Data --> Table --> You can either select Existing Worksheet (enter the cell reference where you want to place the imported table) or New Worksheet and press Ok

IMPORT DATA

• Done Full_Explanation Power_Query