SUM a Group in a list

[Plokimu77]

	A	B	C	D	E	F	G
1					chi.a	Dal.a
2					chi.b	Dal.b	Den.
3	Stores	Volume			Chicago	Dallas	Denver
4	Chi. a	2			6	7	9
5	Dal. a	4
6	Chi. b	4
7	Dal. b	3
8	Den.	9

<tbody>
</tbody>

I would to know of a formula that would sum up the totals for a group, that is define in some cells.
For example if the Chicago group consist of stores Chi.a and Chi.b that are defined in cells E1 and E2.
I would the total of both groups in cell E4. ie. Chi.a + Chi.b = 6
I tried the sumifs but it only work with one criteria. =SUMIFS(B4:B8,A4:A8,E1)
as soon an put the second criteria (cell E2) it returns a 0.

Any help would be appreciated.

 

[Marcelo Branco]

Try

E4
=SUMPRODUCT(SUMIF($A$4:$A$8,E$1:E$2,$B$4:$B$8))
copy across

Note that E1 and E2 must be equal respectively to A4 and A6. i.e. , Chi. a and Chi. b (not chi.a or chi.b)
The same idea for F1, F2 ang G2

Hope this helps

M.

 

[Momentman]

One way to do it (with 2 criteria)

Excel 2010
	A	B	C	D	E	F	G	H
1					chi.a	Dal.a
2					chi.b	Dal.b	Den.
3	Stores	Volume			Chicago	Dallas	Denver
4	Chi.a	2			6	7	9
5	Dal. a	4
6	Chi.b	4						6
7	Dal. b	3
8	Den.	9
Sheet5

Cell Formulas
Range	Formula
H6	=SUMIF(A4:A8,E1,B4:B8)+SUMIF(A4:A8,E2,B4:B8)

Note also that in the text you provided, the criteria are "chi.a and chi.b" but real data has a space before the a and b like "chi a" and "chi b"