Sumifs+Index/Match+Lookup

[Gcsingh90]

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

EXPECTED OUTPUT FROM TABLE 2

Table 3
Month	Name	Yes+No	Yes	%
Jan	Ram	2	2
Jan	K	3	2
Jan	Chetan	2	1
Feb	Ram	3	2
Feb	K	3	1
Feb	Chetan	2	1

I am trying to count the sum of yes+no in table from table per person when the criteria of month and name matches. I tried to use sumif. Kindly help.

 

[awoohaw]

This can be done in Power Query pretty quickly:


let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Status", type text}, {"Ram", Int64.Type}, {"K", Int64.Type}, {"Chetan", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Status"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Status]), "Status", "Value", List.Sum),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Yes & No", each [Yes]+[No]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Month", "Attribute", "Yes & No", "N/A"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"No"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each ([#"Yes & No"])/([#"Yes & No"]+[#"N/A"])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Percentage.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"N/A"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Pct"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Month", "Attribute", "Yes & No", "Yes", "Pct"})
in
#"Reordered Columns1"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 2
2
3	Month	Status	Ram	K	Chetan
4	Jan	Yes	2	2	1
5	Jan	No	0	1	1
6	Jan	N/A	3	2	3
7	Feb	Yes	2	1	1
8	Feb	No	1	2	1
9	Feb	N/A	0	0	1
10
11	EXPECTED OUTPUT FROM TABLE 2
12
13	Table 3						Month	Attribute	Yes & No	Yes	Pct
14							Jan	Ram	2	2	40.00%
15	Month	Name	Yes+No	Yes	%		Jan	K	3	2	60.00%
16	Jan	Ram	2	2			Jan	Chetan	2	1	40.00%
17	Jan	K	3	2			Feb	Ram	3	2	100.00%
18	Jan	Chetan	2	1			Feb	K	3	1	100.00%
19	Feb	Ram	3	2			Feb	Chetan	2	1	66.67%
20	Feb	K	3	1
21	Feb	Chetan	2	1
22
23
Sheet2

 

[Fluff]

A formula option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month	Status	Ram	K	Chetan		Month	Name	Yes+No	Yes
2	Jan	Yes	2	2	1		Jan	Ram	2	2
3	Jan	No	0	1	1		Jan	K	3	2
4	Jan	N/A	3	2	3		Jan	Chetan	2	1
5	Feb	Yes	2	1	1		Feb	Ram	3	2
6	Feb	No	1	2	1		Feb	K	3	1
7	Feb	N/A	0	0	1		Feb	Chetan	2	1
Data

Cell Formulas
Range	Formula
I2:I7	I2	=SUM(SUMIFS(XLOOKUP(H2,$C$1:$E$1,$C$2:$E$7),$A$2:$A$7,G2,$B$2:$B$7,{"yes","no"}))
J2:J7	J2	=SUMIFS(XLOOKUP(H2,$C$1:$E$1,$C$2:$E$7),$A$2:$A$7,G2,$B$2:$B$7,"yes")