Summing an array based on multiple criteria (one column, one row)

[s0up2up]

Hi guys,

I am trying to sum an array based on multiple criteria, one being found in a row and the other found in a column. I've created an example below;

Sum the following variables:
Location :: A
Year :: 31/03/2015
Output :: This should output anything within the appropriate year, that has an A location.

[TABLE="width: 295"]
<tbody>[TR]
[TD]Location [/TD]
[TD]31/03/2014[/TD]
[TD]31/03/2015[/TD]
[TD]31/03/2016[/TD]
[TD]31/03/2017[/TD]
[TD]31/03/2018[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 27[/TD]
[TD] 54[/TD]
[TD] 42[/TD]
[TD] 60[/TD]
[TD] 66[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 63[/TD]
[TD] 12[/TD]
[TD] 16[/TD]
[TD] 39[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] 15[/TD]
[TD] 77[/TD]
[TD] 72[/TD]
[TD] 30[/TD]
[TD] 57[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 20[/TD]
[TD] 43[/TD]
[TD] 48[/TD]
[TD] 34[/TD]
[TD] 73[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 72[/TD]
[TD] 79[/TD]
[TD] 62[/TD]
[TD] 32[/TD]
[TD] 51[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 42[/TD]
[TD] 11[/TD]
[TD] 6[/TD]
[TD] 23[/TD]
[TD] 70[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 26[/TD]
[TD] 64[/TD]
[TD] 15[/TD]
[TD] 63[/TD]
[TD] 44[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] 77[/TD]
[TD] 44[/TD]
[TD] 3[/TD]
[TD] 8[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 74[/TD]
[TD] 68[/TD]
[TD] 75[/TD]
[TD] 66[/TD]
[TD] 46[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] 18[/TD]
[TD] 62[/TD]
[TD] 49[/TD]
[TD] 69[/TD]
[TD] 32[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 33[/TD]
[TD] 16[/TD]
[TD] 41[/TD]
[TD] 31[/TD]
[TD] 56[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 33[/TD]
[TD] 10[/TD]
[TD] 23[/TD]
[TD] 30[/TD]
[TD] 30[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 24[/TD]
[TD] 21[/TD]
[TD] 50[/TD]
[TD] 64[/TD]
[TD] 69[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 5[/TD]
[TD] 50[/TD]
[TD] 28[/TD]
[TD] 19[/TD]
[TD] 48[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 38[/TD]
[TD] 18[/TD]
[TD] 45[/TD]
[TD] 24[/TD]
[TD] 31[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 43[/TD]
[TD] 26[/TD]
[TD] 44[/TD]
[TD] 3[/TD]
[TD] 73[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 6[/TD]
[TD] 32[/TD]
[TD] 72[/TD]
[TD] 38[/TD]
[TD] 54[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 18[/TD]
[TD] 75[/TD]
[TD] 48[/TD]
[TD] 59[/TD]
[TD] 24[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 4[/TD]
[TD] 25[/TD]
[TD] 52[/TD]
[TD] 41[/TD]
[TD] 6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 38[/TD]
[TD] 19[/TD]
[TD] 67[/TD]
[TD] 1[/TD]
[TD] 19[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 3[/TD]
[TD] 45[/TD]
[TD] 38[/TD]
[TD] 25[/TD]
[TD] 25[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 59[/TD]
[TD] 27[/TD]
[TD] 72[/TD]
[TD] 29[/TD]
[TD] 73[/TD]
[/TR]
</tbody>[/TABLE]

Sorry if the example is weird, just roll with it.

I've tried SUMIFS, SUM(INDEXMATCH)s, etc and I just can't get it to work, does any one have any ideas?

Only requirement is that it isn't VBA, it has to be done via formula.

Any questions throw them in the comments.

s0up2up

 

[Eric W]

Here's one option:

	A	B	C	D	E	F	G	H	I	J
A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]31/03/2014[/TD]
[TD="bgcolor: #FAFAFA"]31/03/2015[/TD]
[TD="bgcolor: #FAFAFA"]31/03/2016[/TD]
[TD="bgcolor: #FAFAFA"]31/03/2017[/TD]
[TD="bgcolor: #FAFAFA"]31/03/2018[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Sum[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]54[/TD]
[TD="bgcolor: #FAFAFA, align: right"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"]60[/TD]
[TD="bgcolor: #FAFAFA, align: right"]66[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]31/03/2015[/TD]
[TD="align: right"]126[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]39[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]77[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"]48[/TD]
[TD="bgcolor: #FAFAFA, align: right"]34[/TD]
[TD="bgcolor: #FAFAFA, align: right"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72[/TD]
[TD="bgcolor: #FAFAFA, align: right"]79[/TD]
[TD="bgcolor: #FAFAFA, align: right"]62[/TD]
[TD="bgcolor: #FAFAFA, align: right"]32[/TD]
[TD="bgcolor: #FAFAFA, align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]64[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63[/TD]
[TD="bgcolor: #FAFAFA, align: right"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]E[/TD]
[TD="bgcolor: #FAFAFA, align: right"]77[/TD]
[TD="bgcolor: #FAFAFA, align: right"]44[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]74[/TD]
[TD="bgcolor: #FAFAFA, align: right"]68[/TD]
[TD="bgcolor: #FAFAFA, align: right"]75[/TD]
[TD="bgcolor: #FAFAFA, align: right"]66[/TD]
[TD="bgcolor: #FAFAFA, align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]F[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]62[/TD]
[TD="bgcolor: #FAFAFA, align: right"]49[/TD]
[TD="bgcolor: #FAFAFA, align: right"]69[/TD]
[TD="bgcolor: #FAFAFA, align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"]31[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]50[/TD]
[TD="bgcolor: #FAFAFA, align: right"]64[/TD]
[TD="bgcolor: #FAFAFA, align: right"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]50[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]44[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]32[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72[/TD]
[TD="bgcolor: #FAFAFA, align: right"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]75[/TD]
[TD="bgcolor: #FAFAFA, align: right"]48[/TD]
[TD="bgcolor: #FAFAFA, align: right"]59[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]67[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]59[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72[/TD]
[TD="bgcolor: #FAFAFA, align: right"]29[/TD]
[TD="bgcolor: #FAFAFA, align: right"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=SUMIF(A2:A23,H2,OFFSET(B2:B23,0,MATCH(I2,B1:F1,0)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[goalman14]

=sumproduct((b2:f23)*(b1:f1=h1)*(a2:a23=i1))

 

[s0up2up]

Thanks Erik W and Goalman14 both work equally well.

Erik:
It never occurred to me to use an Offset, as soon as I saw that thought it was an awesomely creative move.

Goalman:
I had never seen a Sumproduct formula before, and it's kinda poorly documented online, so thanks for demonstrating it to me.

Thanks again,
s0up2up

 

[Timmtamm]

For more information on Sumproduct:
http://xldynamic.com/source/xld.sumproduct.html

 

[Eric W]

Glad we could help. If you click Help in Excel and ask about SUMPRODUCT, you'll get a very basic explanation of what it does. Which doesn't show 1% of its power. It can do some amazing things, like Timmtamm's link shows, and others.

In this case, it's probably a better choice than my offering, since it doesn't use the volatile OFFSET function.

Good luck.