Multiple XlookUp Solution

[Gcsingh90]

Hi, I am trying to solve this with Xlookup, Countifs, and Sumifs. Table 1 is the Main data table. Table 2 is fetching data from Table 1. Table 2 counts the number of yes and no and N/A for each name by month from Table 1

Table 1
Month	Date	Ram	K	Chetan
Jan	1-Jan	Yes	Yes	No
Jan	7-Jan	N/A	No	N/A
Jan	4-Jan	Yes	Yes	N/A
Jan	26-Jan	N/A	N/A	Yes
Jan	29-Jan	N/A	N/A	N/A
Feb	21-Feb	Yes	No	Yes
Feb	22-Feb	Yes	Yes	N/A
Feb	23-Feb	No	No	No

Table 2 Required Output: As for Jan month "Yes" for Ram is the returning count of 2 for K is 2 and for Chetan is 1 respectively. I tried Xlookup with countifs but not sure where is the error.

Table 2
Month	Status	Ram	K	Chetan
Jan	Yes	2	2	1
Jan	No	0	1	1
Jan	N/A	3	2	3
Feb	Yes	2	1	1
Feb	No	1	2	1
Feb	N/A	0	0	1

Any help is appreciated. Thanks

 

[Fluff]

You have already got a solution for the table3 so I have removed that form this post, as it's a duplicate of your previous thread.
In future please do not duplicate your questions.

 

[alansidman]

To bet Table 2, I brought the Table 1 into Power Query and unpivoted the data to a normalized table and then was able to pivot the data to your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Month	Date	Ram	K	Chetan		Count of Attribute	Column Labels
2	Jan	1-Jan	Yes	Yes	No			N/A			No			Yes
3	Jan	7-Jan	N/A	No	N/A		Row Labels	Chetan	K	Ram	Chetan	K	Ram	Chetan	K	Ram
4	Jan	4-Jan	Yes	Yes	N/A		Jan	3	2	3	1	1		1	2	2
5	Jan	26-Jan	N/A	N/A	Yes		Feb	1			1	2	1	1	1	2
6	Jan	29-Jan	N/A	N/A	N/A
7	Feb	21-Feb	Yes	No	Yes
8	Feb	22-Feb	Yes	Yes	N/A
9	Feb	23-Feb	No	No	No
Sheet1

 

[Gcsingh90]

To bet Table 2, I brought the Table 1 into Power Query and unpivoted the data to a normalized table and then was able to pivot the data to your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Month	Date	Ram	K	Chetan		Count of Attribute	Column Labels
2	Jan	1-Jan	Yes	Yes	No			N/A			No			Yes
3	Jan	7-Jan	N/A	No	N/A		Row Labels	Chetan	K	Ram	Chetan	K	Ram	Chetan	K	Ram
4	Jan	4-Jan	Yes	Yes	N/A		Jan	3	2	3	1	1		1	2	2
5	Jan	26-Jan	N/A	N/A	Yes		Feb	1			1	2	1	1	1	2
6	Jan	29-Jan	N/A	N/A	N/A
7	Feb	21-Feb	Yes	No	Yes
8	Feb	22-Feb	Yes	Yes	N/A
9	Feb	23-Feb	No	No	No
Sheet1

Thanks, can you help me with countifs and xlookup specifically. Data is not that clean like I provided.