Sum up matching row and column

[icytuvi]

Hello everyone,

I would like to seek your help with a formula that can help sum up matching row and colum as below (I tried sumifs but did not work).

Thank you very much.

Q.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2		Jan	Feb	Mar	Apr			Jan	Feb	Mar	Apr
3	Carrot	20	20	20	20		Apple	#VALUE!
4	Mango	50	50	50	50		Carrot
5	Apple	70	70	70	70		Durian
6	Durian	30	30	30	30		Mango
7	Mango	10	10	10	10
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMIFS($B$3:$E$7,$A$3:$A$7,$G3,$B$2:$E$2,H$2)

 

[KRice]

Give this a try. I wasn't sure that you actually need a two-way lookup, but this incorporates a match to ensure the correct column is being summed (the INDEX/MATCH), and then the SUMPRODUCT adds the entries that match the row criteria.

MrExcel20200502_b.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Jan	Feb	Mar	Apr			Jan	Feb	Mar	Apr
3	Carrot	20	20	20	20		Apple	70	70	70	70
4	Mango	50	50	50	50		Carrot	20	20	20	20
5	Apple	70	70	70	70		Durian	30	30	30	30
6	Durian	30	30	30	30		Mango	60	60	60	60
7	Mango	10	10	10	10
Sheet2

Cell Formulas
Range	Formula
H3:K6	H3	=SUMPRODUCT(INDEX($B$3:$E$7,,MATCH(H$2,$B$2:$E$2,0)),--($G3=$A$3:$A$7))

 

[icytuvi]

Give this a try. I wasn't sure that you actually need a two-way lookup, but this incorporates a match to ensure the correct column is being summed (the INDEX/MATCH), and then the SUMPRODUCT adds the entries that match the row criteria.

MrExcel20200502_b.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		Jan	Feb	Mar	Apr			Jan	Feb	Mar	Apr
3	Carrot	20	20	20	20		Apple	70	70	70	70
4	Mango	50	50	50	50		Carrot	20	20	20	20
5	Apple	70	70	70	70		Durian	30	30	30	30
6	Durian	30	30	30	30		Mango	60	60	60	60
7	Mango	10	10	10	10
Sheet2

Cell Formulas
Range	Formula
H3:K6	H3	=SUMPRODUCT(INDEX($B$3:$E$7,,MATCH(H$2,$B$2:$E$2,0)),--($G3=$A$3:$A$7))

Thank you, Kirk. You have no idea you did me a great help. I really needed the sum with that two-way look up.

 

[KRice]

You're welcome...glad to help. Incidentally, while this solution ensures that, for example, the January column in the summary table matches the January column in the original data table, it will not find multiple matches...meaning that if there were two or more "Jan" columns in the original data table, it would find only the first. Is that a problem? Put differently, in this hypothetical example, do you need a solution that would find all matching "Jan" columns?

 

[KRice]

If you have multiple columns with the same name, you'll need a different approach. A solution is shown to the right in O2:S6. I've added a couple of columns with duplicate names to the data table to demonstrate:

MrExcel20200502_b.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Jan	Feb	Mar	Jan	Apr	Feb			Jan	Feb	Mar	Apr			Jan	Feb	Mar	Apr
3	Carrot	20	20	20	50	20	10		Apple	70	70	70	70		Apple	100	100	70	70
4	Mango	50	50	50	70	50	20		Carrot	20	20	20	20		Carrot	70	30	20	20
5	Apple	70	70	70	30	70	30		Durian	30	30	30	30		Durian	40	70	30	30
6	Durian	30	30	30	10	30	40		Mango	60	60	60	60		Mango	150	130	60	60
7	Mango	10	10	10	20	10	50
Sheet2

Cell Formulas
Range	Formula
J3:M6	J3	=SUMPRODUCT(INDEX($B$3:$G$7,,MATCH(J$2,$B$2:$G$2,0)),--($I3=$A$3:$A$7))
P3:S6	P3	=SUMPRODUCT($B$3:$G$7*(P$2=$B$2:$G$2)*($O3=$A$3:$A$7))

 

[icytuvi]

If you have multiple columns with the same name, you'll need a different approach. A solution is shown to the right in O2:S6. I've added a couple of columns with duplicate names to the data table to demonstrate:

MrExcel20200502_b.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Jan	Feb	Mar	Jan	Apr	Feb			Jan	Feb	Mar	Apr			Jan	Feb	Mar	Apr
3	Carrot	20	20	20	50	20	10		Apple	70	70	70	70		Apple	100	100	70	70
4	Mango	50	50	50	70	50	20		Carrot	20	20	20	20		Carrot	70	30	20	20
5	Apple	70	70	70	30	70	30		Durian	30	30	30	30		Durian	40	70	30	30
6	Durian	30	30	30	10	30	40		Mango	60	60	60	60		Mango	150	130	60	60
7	Mango	10	10	10	20	10	50
Sheet2

Cell Formulas
Range	Formula
J3:M6	J3	=SUMPRODUCT(INDEX($B$3:$G$7,,MATCH(J$2,$B$2:$G$2,0)),--($I3=$A$3:$A$7))
P3:S6	P3	=SUMPRODUCT($B$3:$G$7*(P$2=$B$2:$G$2)*($O3=$A$3:$A$7))

Big thanks for the extra support xD xD xD ~~~ I definitely need it for rearranging my report

 

[KRice]

You're welcome.