sum array with row & column with multiple variables

[nularry]

I have a database with different types of membership types in row 1; they do repeat depending on the member. There is actually 200 members with a total of 10 different types. In column 1 I have what they spent money on; again they do repeat. There is actually 15 different spending categories.
The object is to have a formula in cell B11 (Guest & Dues) add all the data that have that combination from the data field B2:G7.

Thank you for your help, I am really struggling with this.

Row/Column	A	B	C	D	E	F	G
1​		Guest	Individual	Individual	Family	Family	Guest
2​	Food	5	7	3	2	1	5
3​	Dues	2	6	2	8	4	3
4​	Event	9	8	6	2	1	1
5​	Event	6	5	9	5	4	6
6​	Food	5	7	3	2	3	4
7​	Dues	1	5	3	7	4	2
8​
​
10​		Guest	Family	Individual
11​	Dues	Need Formula	Need Formula	Need Formula
12​	Event	Need Formula	Need Formula	Need Formula
13​	Food	Need Formula	Need Formula	Need Formula
	Answer
		Guest	Family	Individual
	Dues	8	23	16
	Event	22	12	28
	Food	19	8	20

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

23 09 09.xlsm
	A	B	C	D	E	F	G
1		Guest	Individual	Individual	Family	Family	Guest
2	Food	5	7	3	2	1	5
3	Dues	2	6	2	8	4	3
4	Event	9	8	6	2	1	1
5	Event	6	5	9	5	4	6
6	Food	5	7	3	2	3	4
7	Dues	1	5	3	7	4	2
8
9
10		Guest	Family	Individual
11	Dues	8	23	16
12	Event	22	12	28
13	Food	19	8	20
nularry

Cell Formulas
Range	Formula
B11:D13	B11	=SUMPRODUCT(($B$1:$G$1=B$10)*($A$2:$A$7=$A11),$B$2:$G$7)

 

[nularry]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

23 09 09.xlsm
	A	B	C	D	E	F	G
1		Guest	Individual	Individual	Family	Family	Guest
2	Food	5	7	3	2	1	5
3	Dues	2	6	2	8	4	3
4	Event	9	8	6	2	1	1
5	Event	6	5	9	5	4	6
6	Food	5	7	3	2	3	4
7	Dues	1	5	3	7	4	2
8
9
10		Guest	Family	Individual
11	Dues	8	23	16
12	Event	22	12	28
13	Food	19	8	20
nularry

Cell Formulas
Range	Formula
B11:D13	B11	=SUMPRODUCT(($B$1:$G$1=B$10)*($A$2:$A$7=$A11),$B$2:$G$7)

Thank you, I forgot about sumproduct.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your version details.