SUMPRODUCT application?

[BlackBear277]

Hello All,

I'm new to the board and using Excel 2010 with Windows 7.

In my example, I have a "Selections" section which I'm able to hardkey parameters (such as Company "1003", Date "9/30/2011", and Product "Pears"). I'm interested in how I might incorporate a SUMPRODUCT formula in my scenerio to return the sum of any values that meet my hardkeyed parameter values. I've struggled writing a formula that fits this specific format.

Thanks so much for any help!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1001	1001	1001	1001	1003	1003	1003	1003	1006	1006	1006	1006
2	Categories:	3/31/2011	6/30/2011	9/30/2011	12/31/2011	3/31/2011	6/30/2011	9/30/2011	12/31/2011	3/31/2011	6/30/2011	9/30/2011	12/31/2011
3	Apples	200	150	250	200	200	175	225	225	250	200	275	225
4	Pears	50	25	75	50	50	25	25	75	100	25	30	50
5	Oranges	25	35	25	25	30	25	40	25	45	25	25	50
6
7		275	210	350	275	280	225	290	325	395	250	330	325
8
9	Selections:
10	1003
11	9/30/2011
12	Pears	???
Sheet1

Excel 2010

 

[Jonmo1]

Are the dates in row 2, duplicates for each name (1001 1003 etc)
I mean, is F2:I2 the same as B2:E2 and J2:M2 ?

If So, try

=INDEX(OFFSET(B3:E5,0,MATCH(A10,B1:M1,0)-1),MATCH(A12,A3:A5,0),MATCH(A11,B2:E2,0))

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1001	1001	1001	1001	1003	1003	1003	1003	1006	1006	1006	1006
2	Categories:	03/31/2011	06/30/2011	09/30/2011	12/31/2011	03/31/2011	06/30/2011	09/30/2011	12/31/2011	03/31/2011	06/30/2011	09/30/2011	12/31/2011
3	Apples	200	150	250	200	200	175	225	225	250	200	275	225
4	Pears	50	25	75	50	50	25	25	75	100	25	30	50
5	Oranges	25	35	25	25	30	25	40	25	45	25	25	50
6
7		275	210	350	275	280	225	290	325	395	250	330	325
8
9	Selections:
10	1001
11	09/30/2011
12	Apples	250
Sheet2

 

[Aladin Akyurek]

Hello All,

I'm new to the board and using Excel 2010 with Windows 7.

In my example, I have a "Selections" section which I'm able to hardkey parameters (such as Company "1003", Date "9/30/2011", and Product "Pears"). I'm interested in how I might incorporate a SUMPRODUCT formula in my scenerio to return the sum of any values that meet my hardkeyed parameter values. I've struggled writing a formula that fits this specific format.

Thanks so much for any help!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1001	1001	1001	1001	1003	1003	1003	1003	1006	1006	1006	1006
2	Categories:	3/31/2011	6/30/2011	9/30/2011	12/31/2011	3/31/2011	6/30/2011	9/30/2011	12/31/2011	3/31/2011	6/30/2011	9/30/2011	12/31/2011
3	Apples	200	150	250	200	200	175	225	225	250	200	275	225
4	Pears	50	25	75	50	50	25	25	75	100	25	30	50
5	Oranges	25	35	25	25	30	25	40	25	45	25	25	50
6
7		275	210	350	275	280	225	290	325	395	250	330	325
8
9	Selections:
10	1003
11	9/30/2011
12	Pears	???
Sheet1

Excel 2010

B12, control+shift+enter, not just enter:

=SUM(INDEX($B$3:$M$5,MATCH(A2,$A$3:$A$5,0),MATCH(1,IF($B$1:$M$1=A10,IF($B$2:$M$2=A11,1)),0)))

 

[BlackBear277]

Many thanks for the help Aladin Akyurek & jonmo1!

Seems to have done the trick!

I will do some reading on Ctrl+Shift+Enter (CSE formulas) in Excel.

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[Aladin Akyurek]

Many thanks for the help Aladin Akyurek & jonmo1!

Seems to have done the trick!

I will do some reading on Ctrl+Shift+Enter (CSE formulas) in Excel.

You are welcome. Thanks for providing us feedback.

 

[T. Valko]

I will do some reading on Ctrl+Shift+Enter (CSE formulas) in Excel.

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Yahya]

Aladin i think you mean A12 not A2
=SUM(INDEX($B$3:$M$5,MATCH(A12,$A$3:$A$5,0),MATCH(1,IF($B$1:$M$1=A10,IF($B$2:$M$2=A11,1)),0))

 

[Rebuild8]

Why not just use SUMPRODUCT? (unless I am missing something)

=SUMPRODUCT((B3:M5)*(A3:A5=A11)*(B1:M1=A9)*(B2:M2=A10))

 

[Aladin Akyurek]

Aladin i think you mean A12 not A2
=SUM(INDEX($B$3:$M$5,MATCH(A12,$A$3:$A$5,0),MATCH(1,IF($B$1:$M$1=A10,IF($B$2:$M$2=A11,1)),0))

Right, obviously. Thanks. Although a slow typer, I manage to miss some key strokes on my vaio I'm nevertheles happy with.