Index and Match matrix sum

[slamanager]

Hi,

can someone give me an example of a Index and Match to look at a table and give me the sum of the values.

col A contains the numbers (a4:a9)
1
2
3
1
2
3

B3 has the value "A" C3 has "B" up to G3 which has "F"

this matrix is populated with numbers its actually a pivot table pasted as values for 2 days three lines per day, i'm trying to build a formula matrix to total up all the values for the row and column inputs.

sum all the 1's in col B if the header is "A" the data is varible each day and i can get up to 100 unique returns and maybe not for each column.


Thanks

A,B,C,D,E,F
1 10,22,33,22,1,7
2 8,9,7,6,8,9
3 2,3,4,5,1,1
1 22,44,33,22,11,2
2 66,44,22,11,88,99
3 2,5,6,7,8,9

A/1 = 32
B/1 = 66

 

[konew1]

Hi
Index/match will only return a single value, not a range to sum
Use sumif or sumifs or sumproduct

Excel Workbook
	A	B	C	D	E	F	G
1		A	B	C	D	E	F
2	1	10	22	33	22	1	7
3	2	8	9	7	6	8	9
4	3	2	3	4	5	1	1
5	1	22	44	33	22	11	2
6	2	66	44	22	11	88	99
7	3	2	5	6	7	8	9
8
9	1	32	66	66	44	12	9
10	2	74	53	29	17	96	108
11	3	4	8	10	12	9	10
Sheet1

 

[bosco_yip]

1] Your data in A3:G9 with heading A,B,C,D….in B3:G3

2] A12,B12 and A13,B13, enter A,1 and B,1

3] In C12, enter formula and copy down :

=SUMIF(A$4:A$9,B12,INDEX(B$4:G$9,,MATCH(A12,B$3:G$3,0)))

Regards

 

[slamanager]

So this formula only references the row not the column is this possible to get both.

 

[slamanager]

many thanks tested and all ok