3D Array Sum Formula

[bromy2004]

Hi Everyone,

i'm trying to enter a 3D sum array into my spreadsheet...but its not returning the values.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
4	*	A	A	A	B	B	B	C	C	C
5	*	i	ii	iii	i	ii	iii	i	ii	iii
6	100	60	49	73	8	17	58	39	30	13
7	200	85	75	42	7	59	31	35	37	16
8	300	24	78	79	12	64	71	83	18	56
9	400	48	86	70	88	6	2	19	45	62
10	500	82	26	14	65	3	72	10	41	28
11	600	34	47	15	23	4	53	90	74	5
12	700	68	22	69	38	84	67	66	44	1
13	800	63	43	89	54	27	57	51	52	20
14	900	21	77	87	76	61	50	55	36	9
15	1000	33	29	32	81	40	11	25	80	46
16	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	0	*	*	*	*	*	*	*	*
19	*	100	*	*	*	*	*	*	*	*
20	*	A	*	*	*	*	*	*	*	*
21	*	i	*	*	*	*	*	*	*	*
Sheet1

but its not bringing back any values with any combination.
can anyone suggest a better formula?

 

[wsjackman]

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C18</TD><TD>{=SUM(IF(AND($B$6:$B$15=C19,$C$4:$K$4=C20,$C$5:$K$5=C21),$C$6:$K$15,0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

but its not bringing back any values with any combination.
can anyone suggest a better formula?

[/QUOTE]

try instead
=SUMPRODUCT((C4:K4=C20)*(C5:K5=C21)*OFFSET(B5,MATCH(C19,B6:B15,0),1,1,9))

 

[bromy2004]

thank you that works perfectly.

can you please help me understand how it gets the result

-bromy

 

[Aladin Akyurek]

AND() cannot be used the way you attempt for it will fail to return a proper array oobject...

The following would be the way you intended to process the data:

Control+shift+enter, not just enter...

=SUM(IF($B$6:$B$15=C19,IF($C$4:$K$4=C20,IF($C$5:$K$5=C21,$C$6:$K$15))))

 

[bromy2004]

Thank you Aladin Akyurek,

yours works perfectly and is easy to understand.

thanks

 

[wsjackman]

The sumproduct() function is a very powerful function which you will see used very often for a large variety of applications in the forum. it allows you to put together formulas with complex limits such as your two different rows = different cells and summing a third row based on a match.

It is also an array based formula which you do not have to use the control-shift-enter to confirm. One of its main limitations is that all of the arrays have to be the same size, thus I used the offset function to limit the data set to have the same size array as the limits array.

If you follow the forum, you will see there are normally multiple ways to solve a given problem. two advantages to this, First that if you get locked in a corner with one method, try a different direction (I had tried several if statements which were not working before I went to the sumproduct) Second I often double check a complex formula with a second method. If they give me the same answer I am normally comfortable that I got it right. If the do not give me the same answer, I have sometimes had to go to a third method to figure out which was correct.

 

[cristib]

I have tried just

{=SUM(IF(($B$6:$B$15=$C$19)*($C$4:$K$4=$C$20)*($C$5:$K$5=$C$21);$C$6:$K$15;0))}

and it worked.

(Change ; to ,)