Sum values with horizontal and vertical criteria

[colinheslop1984]

How do I SUM values where one criteria is on vertical axis then the another criteria is on the horizontal axis? I.E, if I wanted to sum the BEACH for June only

[TABLE="width: 704"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]WEEK[/TD]
[TD]BEACH [/TD]
[TD]DENIM[/TD]
[TD]DRESSES[/TD]
[TD]JERSEY [/TD]
[TD]KNITWEAR[/TD]
[TD]NIGHTWEAR[/TD]
[TD]OUTERWEAR[/TD]
[TD]SHIRTS[/TD]
[TD]SHORTS[/TD]
[/TR]
[TR]
[TD]JUNE[/TD]
[TD]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]JUNE[/TD]
[TD]2[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]382[/TD]
[TD="align: right"]1362[/TD]
[TD="align: right"]956[/TD]
[TD="align: right"]1169[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]2112[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]JUNE[/TD]
[TD]3[/TD]
[TD="align: right"] 300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JUNE[/TD]
[TD]4[/TD]
[TD="align: right"] 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JULY[/TD]
[TD]5[/TD]
[TD="align: right"] 455[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="9"></colgroup>[/TABLE]

 

[Saba Sabaratnam]

Hi

Enter your data into A1:K6 to understand the formula

Enter month in A8

Enter Beach in A9

Enter the following formula into B9

=SUMIFS(INDEX(C2:$K$6,0,MATCH(A9,$C$1:$K$1,0)),$A$2:$A$6,A8)

Let me know how you go.

Kind regards

Saba

 

[colinheslop1984]

Hi

Enter your data into A1:K6 to understand the formula

Enter month in A8

Enter Beach in A9

Enter the following formula into B9

=SUMIFS(INDEX(C2:$K$6,0,MATCH(A9,$C$1:$K$1,0)),$A$2:$A$6,A8)

Let me know how you go.

Kind regards

Saba

Thanks but I get an error #N/A

 

[jtakw]

Hi,

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	MONTH	WEEK	BEACH	DENIM	DRESSES	JERSEY	KNITWEAR	NIGHTWEAR	OUTERWEAR	SHIRTS	SHORTS
2	JUNE	1	0	2	1	1	1	1	1	1	1
3	JUNE	2	255	382	1362	956	1169	223	249	2112	53
4	JUNE	3	300
5	JUNE	4	100
6	JULY	5	455
7
8	Criteria	BEACH
9	Month	JUNE
10	Result	655
Sheet102

Cell Formulas
Range	Formula
B10	=SUMPRODUCT((A2:A6=B9)*(C1:K1=B8)*C2:K6)

 

[Aladin Akyurek]

Thanks but I get an error #N/A

That's strange. Using hard-coded look up values...

=SUMIFS(INDEX($C$2:$K$6,0,MATCH("BEACH",$C$1:$K$1,0)),$A$2:$A$6,"JUNE")

this returns 655.

 

[jtakw]

That Is strange, Aladin.

Tested with TEXT numbers in C2:C6, the SUMIFS/INDEX/MATCH returns 0.
SUMPRODUCT still holds and returns 655.

However, Inserted leading/trailing Space in C1, SUMIFS/INDEX/MATCH returns #N/A error.
SUMPRODUCT returns 0

So OP needs to "clean-up" C1:K1

 

[Saba Sabaratnam]

Thank you Aladin and Jtakw,

colinheslop1984, as suggested by Jtakw, you may want to remove leading and trailing spaces as look value and lookup arrays must be the same. You can use Trim function to do this.

Kind regards

Saba