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.

 

[austinandreikurt]

Hi. Sorry if I just post it and trying to put this one up again as I really need it asap. Thanks!

 

[sandy666]

maybe use XL2BB to post representative example and expected result

 

[austinandreikurt]

Regret but it shouldn't use any codes and just formula. The expected result is 26 for the sum and average of 13, because the 1st instance is 5th on both row and column which is 25 in the table and 1st on both row and column which is 1.

 

[austinandreikurt]

I misunderstood, sorry. Here's the Table in A1:B5:

1​	2​	3​	4​	5​
6​	7​	8​	9​	10​
11​	12​	13​	14​	15​
16​	17​	18​	19​	20​
21​	22​	23​	24​	25​

So say I put Apple in D1, E1 will put on the sum of all "Apple" based on the Columns and Row number from the A1:B5 table and the average in E2

 

[sandy666]

You didn't read my post (post#3) so....
Good luck

 

[austinandreikurt]

Regret on this but we are not allowed to make Add-ins and use VBA in here as there are a lot of restrictions. I am not also easily able to make settings in excel unless permitted by admin.

 

[austinandreikurt]

Can someone please solve this as I will be using the formula in a larger set of data and my shift is about to end. Thanks

 

[Sulprobil]

I have to admit I was guessing a lot. I hope it helps:

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	12	28	14
6	Pea	2	8	16	28	14
7	Apple			0	26	13
Tabelle1

Cell Formulas
Range	Formula
S2:S7	S2	=Q2*R2
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! Can there be a compound formula without the need for Sum and Sum ALL as there are sheets where I only need the average and will not be making the Sum and Sum All Columns.