Suming a ranage based on another range

[Pauljj]

I need to sum up the column C here dependant on whether 'Manager' appears in column A, if it does, it should sum column C dependant on the criteria in range G6:H10

For example as per below, there are 3 managers, one with letter P, one with 10 to 7 and one with 1

In the range G6:H10 letter P = 0, 10 to 7 = 1 and 1 = 1.

So the result should be just 2

Does anyone have any ideas, I have used a range of combinations using SUMPRODUCT with OFFSET functions but keep end up getting #value! errors

Book2
	B	C	D	E	F	G	H
5
6	Manager	P				P	0
7	Manager	10to7				10to7	1
8	Manager	1				1	1
9	Sales	XB				XB	0
10	Sales	10to7				X	0
11	Sales	1
12	Sales	1
13	Sales	10to7
14	Sales	1
15	Sales	1
16	Sales	X
17
18
Sheet1

 

[WinteE]

Add number 0 or 1 with a VLOOKUP() in column D and use SUMPRODUCT() based on columns B and D.

 

[Pauljj]

Thank you for coming back to me, unfortunatly that wouldn't be pracitical as I have only copied in a snapshot of the whole spreadsheet. Column C is actually one of about 80 similar columns so I was hoping to add a formula to sum up, simply in row 4

 

[markmzz]

Try this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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;">6</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">P</td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td><td style="text-align: center;;"></td><td style="text-align: center;;">P</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">XB</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">XB</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="background-color: #FFFF00;;"><- must to be empty</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">D7</th><td style="text-align:left">{=SUM(<font color="Blue">SUBTOTAL(<font color="Red">9,OFFSET(<font color="Green">G6,TRANSPOSE(<font color="Purple">IF(<font color="Teal">A6:A16=C7,MATCH(<font color="#FF00FF">IF(<font color="Navy">A6:A16=C7,B6:B16,0</font>),F6:F10,0</font>),COUNT(<font color="#FF00FF">G6:G10</font>)+1</font>)-1</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Result</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Cod</td><td style="font-weight: bold;text-align: center;;">Value</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">P</td><td style="text-align: center;;">Manager</td><td style="text-align: center;background-color: #C5D9F1;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">P</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">10 to 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">XB</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">XB</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">D6</th><td style="text-align:left">{=SUM(<font color="Blue">SUBTOTAL(<font color="Red">9,OFFSET(<font color="Green">G6,TRANSPOSE(<font color="Purple">IF(<font color="Teal">A6:A16=C6,MATCH(<font color="#FF00FF">IF(<font color="Navy">A6:A16=C6,B6:B16,0</font>),F6:F10,0</font>)</font>)-1</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Pauljj]

Mark it works a treat, just now trying to get my head around how it actually works ?

Thanks so much

 

[markmzz]

I think that the best way to understand the formula is to divide it into parts. Look at this:

Note: I used the SUBTOTAL function and not only the SUM to sum the array created with the function OFFSET because only SUBTOTAL function do this job.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>F</th><th>G</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Col01</td><td style="font-weight: bold;text-align: center;;">Col02</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Result</td><td style="font-weight: bold;text-align: center;;">Cod</td><td style="font-weight: bold;text-align: center;;">Value</td><td style="font-weight: bold;text-align: center;;">Parts of the formula</td><td style="font-weight: bold;text-align: center;;">Results</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">P</td><td style="text-align: center;;">Manager</td><td style="text-align: center;background-color: #C5D9F1;;">2</td><td style="text-align: center;;">P</td><td style="text-align: center;;">0</td><td style="text-align: center;background-color: #C5D9F1;;">=IF(A6:A16=C6,B6:B16,0)</td><td style="text-align: center;background-color: #C5D9F1;;">{"P";"10 to 7";1;0;0;0;0;0;0;0;0}</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">10 to 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;">1</td><td style="text-align: center;background-color: #C5D9F1;;">=MATCH(IF(A6:A16=C6,B6:B16,0),F6:F10,0)</td><td style="text-align: center;background-color: #C5D9F1;;">{1;2;3;#N/D;#N/D;#N/D;#N/D;#N/D;#N/D;#N/D;#N/D}</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Manager</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;background-color: #C5D9F1;;">=IF(A6:A16=C6,MATCH(IF(A6:A16=C6,B6:B16,0),F6:F10,0))-1</td><td style="text-align: center;background-color: #C5D9F1;;">{0;1;2;-1;-1;-1;-1;-1;-1;-1;-1}</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">XB</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">XB</td><td style="text-align: center;;">0</td><td style="text-align: center;background-color: #C5D9F1;;">=OFFSET(G6,TRANSPOSE(IF(A6:A16=C6,MATCH(IF(A6:A16=C6,B6:B16,0),F6:F10,0))-1),0)</td><td style="text-align: center;background-color: #C5D9F1;;">{0\1\1\"Value"\"Value"\"Value"\"Value"\"Value"\"Value"\"Value"\"Value"}</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;">0</td><td style="text-align: center;background-color: #C5D9F1;;">=SUBTOTAL(9,OFFSET(G6,TRANSPOSE(IF(A6:A16=C6,MATCH(IF(A6:A16=C6,B6:B16,0),F6:F10,0))-1),0))</td><td style="text-align: center;background-color: #C5D9F1;;">{0\1\1\0\0\0\0\0\0\0\0}</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">=SUM(SUBTOTAL(9,OFFSET(G6,TRANSPOSE(IF(A6:A16=C6,MATCH(IF(A6:A16=C6,B6:B16,0),F6:F10,0))-1),0)))</td><td style="text-align: center;background-color: #C5D9F1;;">SUM({0\1\1\0\0\0\0\0\0\0\0})</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">10 to 7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">Sales</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">D6</th><td style="text-align:left">{=SUM(<font color="Blue">SUBTOTAL(<font color="Red">9,OFFSET(<font color="Green">G6,TRANSPOSE(<font color="Purple">IF(<font color="Teal">A6:A16=C6,MATCH(<font color="#FF00FF">IF(<font color="Navy">A6:A16=C6,B6:B16,0</font>),F6:F10,0</font>)</font>)-1</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
I hope this help you to understand the formula.

Markmzz

 

[Pauljj]

In one word.......


LEGENDARY


Thank you

 

[markmzz]

In one word.......


LEGENDARY


Thank you

I'm glad to have helped.

And thank you for the feedback.

Markmzz