SUMIFS in a 2-Dimensional Array

[Tarver]

I'm struggling with SUMIFS today for some reason.

I've got an array of cells that contains data based upon a week and year, for different sources which are named in column A, like this:

	A	B	C	D	E
1		WK 1-18	WK 2-18	WK 3-18	WK 4-18
2	Sally	1	2	3	4
3	Joe	4	8	16	32
4	Susie	2	4	6	8
5	James	3	6	9

<tbody>
</tbody>

I need to sum these values by a name, for a range of weeks. The name I need to sum is located in cell A10, and the start week is in B10 and ending week is in C10

What I'm coming up with looks like this:
=SUMIFS(B2:E5,B2:E2,">="&B10,B2:E2,"<="&C10,A2:A5,A10)

This results in a value error.

B2:E5 is the table of values (Sum range)
B2:W2 is the date ranges (Criteria Range 1 and Criteria Range 2)
">="&B10 should, if I'm doing it right, include all values greater than my start week. (Criteria 1)
"<="&C10 should, again, if I'm doing it correctly, include weeks less than my start week. (Criteria 2)
A2:A5 is my range of names, and (Criteria Range 3)
A10 is the criteria to match my target name. (Criteria 3)

Any ideas on what I'm doing wrong? Your help is most appreciated.

I'm not tied to SUMIFS. If there's a better way, I'm glad to hear your suggestions.

 

[Eric W]

Here are 2 options:

Excel 2012

	A	B	C	D	E
1	Name / Week:	1	2	3	4
2	Sally	1	2	3	4
3	Joe	4	8	16	32
4	Susie	2	4	6	8
5	James	3	6	9
6
7
8
9
10	Susie	2	3
11
12	Sum:	10
13	Sum:	10

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

Sheet6​

Worksheet Formulas Cell Formula B12 =SUMPRODUCT(B2:E5*(A2:A5=A10)*(B2:E2>=B10)*(B2:E2<=C10)) B13 =SUMIFS(INDEX(B2:E5,MATCH(A10,A2:A5,0),0),B2:E2,">="&B10,B2:E2,"<="&C10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I think your range descriptions were a little inconsistent, but see how they are set up in my example. Also, notice I just used a number for the week, since it's more difficult to work around text. You could use a custom format if you want, to keep the easier formulas and still see "WK 1".

 

[Marcelo Branco]

Hi Eric,

I think there a typo in your formulas.
Shouldn't it be?
=SUMPRODUCT(B2:E5*(A2:A5=A10)*(B1:E1>=B10)*(B1:E1<=C10))

The same in SUMIFS

M.

 

[Eric W]

The formulas should be:

Cell Formula B12 =SUMPRODUCT(B2:E5*(A2:A5=A10)*(B1:E1>=B10)*(B1:E1<=C10)) B13 =SUMIFS(INDEX(B2:E5,MATCH(A10,A2:A5,0),0),B1:E1,">="&B10,B1:E1,"<="&C10) <tbody> </tbody>

<tbody>
</tbody>

The only worked in my previous post since the values on row 2 were the same as row1.


Edit: Yeah, good catch, Marcelo!

 

[Tarver]

Great answer, thanks! SUMPRODUCT is the way to go.

 

[Eric W]

Glad it works for you!