Sum multiple criteria along X and Y axis

[EdStockton]

I need help in finding totals of multiple criteria along both the X and Y axis.

	Atlanta	Cleveland	Reno	Hamburg	Decatur	Dekalb	Hornbrook	Hilt	Yreka	Redding	Red Bluff	Carson City	Tillimook
Coke	10	4	13	6	8	4	3	2	6	3	2	4	7
Pepsi	1	12	5	43	56	1	2	5	7	9	6	9	2
Milk	6	5	8	8	8	7	7	10	10	10	9	10	12
Water	5	2	4	4	4	6	3	6	5	6	7	5	8
Diet Coke	5	8	9	2	9	7	11	9	4	12	8	13	10
Squirt	25	42	5	6	5	42	42	5	8	16	47	42	6
Orange Juice	12	6	8	4	1	24	7	9	4	12	25	7	10
Apple Juice	44	31	2	8	5	56	31	3	12	14	61	31	3
Grape Juice	62	5	5	9	6	64	5	8	10	18	64	5	12
Tomato Juice	31	81	32	4	4	72	82	32	4	38	76	83	32
Root Beer	52	84	4	2	2	136	84	4	3	93	138	84	4
Coke	Cleveland
Orange Juice	Hilt
Tomato Juice	Red Bluff		Equals a sum of 237

<tbody>
</tbody>

As you can see, I have tree criteria related to the vertical axis and three criteria related to the horizontal axis. In practice, I often have more than three criteria for both axes. I cannot use the sumifs because I would have to change the formula for every use and I will need to use the formula thousands of times.

Any help will be appreciated.

Thank you, Ed Stockton

 

[Eric W]

Try this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Atlanta	Cleveland	Reno	Hamburg	Decatur	Dekalb	Hornbrook	Hilt	Yreka	Redding	Red Bluff	Carson City	Tillimook
2	Coke	10	4	13	6	8	4	3	2	6	3	2	4	7
3	Pepsi	1	12	5	43	56	1	2	5	7	9	6	9	2
4	Milk	6	5	8	8	8	7	7	10	10	10	9	10	12
5	Water	5	2	4	4	4	6	3	6	5	6	7	5	8
6	Diet Coke	5	8	9	2	9	7	11	9	4	12	8	13	10
7	Squirt	25	42	5	6	5	42	42	5	8	16	47	42	6
8	Orange Juice	12	6	8	4	1	24	7	9	4	12	25	7	10
9	Apple Juice	44	31	2	8	5	56	31	3	12	14	61	31	3
10	Grape Juice	62	5	5	9	6	64	5	8	10	18	64	5	12
11	Tomato Juice	31	81	32	4	4	72	82	32	4	38	76	83	32
12	Root Beer	52	84	4	2	2	136	84	4	3	93	138	84	4
13
14	Y-Axis	X-Axis	Sum
15	Coke	Cleveland	237
16	Orange Juice	Hilt
17	Tomato Juice	Red Bluff
18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C15 =SUMPRODUCT(ISNUMBER(MATCH(A2:A12,A15:A17,0))*ISNUMBER(MATCH(B1:N1,B15:B17,0))*B2:N12) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the values you want in the A15:B17 cells and the formula in C15.

 

[EdStockton]

That worked perfect. You made it seem so easy. Thank you very much.

 

[Eric W]

Happy to help.