SUM a Group in a list

Plokimu77

Board Regular
Joined
Oct 1, 2014
Messages
122
A
B
C
D
E
F
G
1
chi.aDal.a
2
chi.bDal.bDen.
3
Stores
Volume
Chicago
Dallas
Denver
4
Chi. a 2679
5
Dal. a4
6
Chi. b4
7
Dal. b3
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.
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Jan 11, 2012
Messages
4,039
Office Version
  1. 365
Platform
  1. Windows
One way to do it (with 2 criteria)

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">chi.a</td><td style="color: #333333;background-color: #FAFAFA;;">Dal.a</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">chi.b</td><td style="color: #333333;background-color: #FAFAFA;;">Dal.b</td><td style="color: #333333;background-color: #FAFAFA;;">Den.</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;font-style: italic;color: #333333;background-color: #FAFAFA;;">Stores</td><td style="font-weight: bold;font-style: italic;color: #333333;background-color: #FAFAFA;;">Volume</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="font-weight: bold;font-style: italic;color: #333333;background-color: #FAFAFA;;">Chicago</td><td style="font-weight: bold;font-style: italic;color: #333333;background-color: #FAFAFA;;">Dallas</td><td style="font-weight: bold;font-style: italic;color: #333333;background-color: #FAFAFA;;">Denver</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;background-color: #FAFAFA;;">Chi.a</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">6</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">7</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">9</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">Dal. a</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;background-color: #FAFAFA;;">Chi.b</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;background-color: #FAFAFA;;">Dal. b</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;background-color: #FAFAFA;;">Den.</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">9</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=SUMIF(<font color="Blue">A4:A8,E1,B4:B8</font>)+SUMIF(<font color="Blue">A4:A8,E2,B4:B8</font>)</td></tr></tbody></table></td></tr></table><br />

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"
 

Watch MrExcel Video

Forum statistics

Threads
1,109,318
Messages
5,527,992
Members
409,796
Latest member
Legion74

This Week's Hot Topics

Top