3D Array Sum Formula

bromy2004

Board Regular
Joined
Feb 8, 2008
Messages
63
Hi Everyone,

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


Excel Workbook
BCDEFGHIJK
4*AAABBBCCC
5*iiiiiiiiiiiiiiiiii
610060497381758393013
720085754275931353716
8300247879126471831856
94004886708862194562
1050082261465372104128
116003447152345390745
1270068226938846766441
13800634389542757515220
1490021778776615055369
151000332932814011258046
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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
<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))
 
Upvote 0
thank you that works perfectly.

can you please help me understand how it gets the result

-bromy
 
Upvote 0
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))))
 
Upvote 0
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.
 
Upvote 0
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 ,)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top