Add sum of multiple columns with two matching criteria?

[bbarkley]

Table 1:

1	A	B	C
2		Apples	Oranges
3	Joe

<tbody>
</tbody>

Table 2:

1	A	B	C	D
2		Apples	Oranges	Apples
3	Joe	5	4	3

<tbody>
</tbody>

I would like to be able to pull the sum of an array of columns if the two criteria (name and "apples" as the example) both match.

So sum if Table 1 A3 = "Joe" AND Table 1 B2 = "Apples". But I would like the sum range to be the whole of table 2, so that it would tell me that Joe has 8 apples total. When I put the sum range as B:D or similar, it gives me the #VALUE error.

 

[Aladin Akyurek]

Let A:C of Sheet1 house table 1; A:D of Sheet2 Table 2.


In B2 of Sheet1, control+shift+enter, not just enter, and copy across:

=SUM(IF(Sheet2!$A$2:$A$10=$A2,IF(Sheet2!$B$1:$D$1=B$1,Sheet2!$B$2:$D$10)))

where Joe is in A2, Apples in B1, and Oranges in C1.

Alternatively, in B2 just enter and copy across:

=SUMIFS(INDEX(Sheet2!$B$2:$D$10,MATCH($A2,Sheet2!$A$2:$A$10,0),0),Sheet2!$B$1:$D$1,B$1)

 

[Eric W]

Another option:

	A	B	C	D
1	A	B	C
2		Apples	Oranges
3	Joe	8	4
4	Mark	7	3
5	Jill	4	2
6
7		Apples	Oranges	Apples
8	Joe	5	4	3
9	Jill	1	2	3
10	Mark	2	3	5

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

</td>
</tbody>

Sheet3

Worksheet Formulas Cell Formula B3 =SUMPRODUCT($B$8:$D$10*($A$8:$A$10=$A3)*($B$7:$D$7=B$2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>