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.

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

<tbody>
</tbody>

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
1	Location	31/03/2014	31/03/2015	31/03/2016	31/03/2017	31/03/2018		Location	Date	Sum
2	C	27	54	42	60	66		A	31/03/2015	126
3	D	63	12	16	39	1
4	E	15	77	72	30	57
5	C	20	43	48	34	73
6	C	72	79	62	32	51
7	A	42	11	6	23	70
8	D	26	64	15	63	44
9	E	77	44	3	8	12
10	A	74	68	75	66	46
11	F	18	62	49	69	32
12	C	33	16	41	31	56
13	A	33	10	23	30	30
14	D	24	21	50	64	69
15	B	5	50	28	19	48
16	A	38	18	45	24	31
17	D	43	26	44	3	73
18	B	6	32	72	38	54
19	C	18	75	48	59	24
20	B	4	25	52	41	6
21	A	38	19	67	1	19
22	C	3	45	38	25	25
23	B	59	27	72	29	73

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula J2 =SUMIF(A2:A23,H2,OFFSET(B2:B23,0,MATCH(I2,B1:F1,0)-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.