SUM the table using the Criteria and Position

[austinandreikurt]

Hi! I need help in trying to sum the values on a table range say it is from A1:B5 based on a criteria. I have a table of criteria in Column P to R like below:

Person	Column	Row
Apple	5​	5​
Orange	1​	1​
Apple	1​	1​

What I need is to get the sum/average values from A1:B5 of all "Apple" based on the column and row specified in Column P to R and it should ignore the blank instances. Say there might be a row for Apple in Column P but no values in Columns Q and R.

 

[Sulprobil]

Sure:

MrExcel_Sum_the_table_using_the_criteria_and_position.xlsx
	V
1	Average w/o empty
2	13
3	1
4	13
5	14
6	14
7	13
Tabelle1

Cell Formulas
Range	Formula
V2:V7	V2	=SUMPRODUCT(--($P2=$P$2:$P$7),$Q$2:$Q$7,$R$2:$R$7)/SUMPRODUCT(--($P2=$P$2:$P$7),--($S$2:$S$7>0))

 

[austinandreikurt]

Hi Sulprobil, from the formula, how will it get the data from A1 to B5 as I cannot see it in the formula? Also, it still give reference to Column S? But I only have the data in A1:B5 and Column P to R in other sheets

 

[Sulprobil]

Sorry, I think I got it now, but just with helper columns:

MrExcel_Sum_the_table_using_the_criteria_and_position.xlsx
	P	Q	R	S	T	U
1	Person	Column	Row	Sum 1	Sum All	Average w/o empty
2	Apple	5	5	25	26	13
3	Orange	1	1	1	1	1
4	Apple	1	1	1	26	13
5	Pea	3	4	18	35	17,5
6	Pea	2	4	17	35	17,5
7	Apple			0	26	13
Tabelle1

Cell Formulas
Range	Formula
S2:S7	S2	=IF(COUNT(Q2:R2)=2,INDIRECT(“R”&R2&”C”&Q2,FALSE),0)
T2:T7	T2	=SUMIF($P$2:$P$7,$P2,$S$2:$S$7)
U2:U7	U2	=T2/SUMPRODUCT(--($P2=$P$2:$P$7),--($S$2:$S$7>0))

Honestly, I do not like INDIRECT. If you could describe the whole process of yours, we might we able to help you in a better way.

 

[Sulprobil]

Without INDIRECT:

MrExcel_Sum_the_table_using_the_criteria_and_position.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	2	3	4	5											Person	Column	Row	Sum 1	Sum All	Average w/o empty
2	6	7	8	9	10											Apple	5	5	25	26	13
3	11	12	13	14	15											Orange	1	1	1	1	1
4	16	17	18	19	20											Apple	1	1	1	26	13
5	21	22	23	24	25											Pea	3	4	18	35	17,5
6																Pea	2	4	17	35	17,5
7																Apple			0	26	13
Tabelle1

Cell Formulas
Range	Formula
S2:S7	S2	=IF(COUNT(Q2:R2)=2,INDEX($A$1:$E$5,$R2,$Q2),0)
T2:T7	T2	=SUMIF($P$2:$P$7,$P2,$S$2:$S$7)
U2:U7	U2	=T2/SUMPRODUCT(--($P2=$P$2:$P$7),--($S$2:$S$7>0))

 

[austinandreikurt]

Thanks Sulprobil. This is enough solution for me. I will just hide the helper columns using Column Grouping. Thanks again!