# SUM a Group in a list

#### Plokimu77

##### Board Regular
 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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Marcelo Branco

##### MrExcel MVP
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

##### Well-known Member
One way to do it (with 2 criteria)

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"

Replies
3
Views
100
Replies
0
Views
39
Replies
1
Views
227
Replies
7
Views
137
Replies
4
Views
56