Formula to add up a values based on a range of account numbers

[howard]

I would like a formula to add up values in Col E based on a range of account numbers in Col C

For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc

Your assistance in this regard is most appreciated

 

[Akashwani]

Hi Howard.

Is this what you mean?....

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1		Acc #		Value		Len Check		Formula1	Formula2
2		3000-3015		100		9		200	200
3		3000-3015A		50		10
4		3000-3015		100		9
5		3000-3015A		50		10
6		3000A-3015		50		10
7		3000A-3015A		50		11
8
Sheet4

I hope that helps.

Ak

 

[howard]

Hi Akashwani

Thanks for the reply. You have an idea of what i'm trying to achieve, but its not exactly what I want.

i will try an explain below as well as with sample data

I want to add all the values in Col E based on a specified range on Col A

Eg 1) I want to add up all the values in Col E where the account numbers in Col A range from 3001 to 3007 excluding Alphas i.e four digit account numbers

2) I want to add values in Col E where the account numbers in Col A are in the range 3001D to 3007D
3) I want to add values in Col E where the account numbers in Col A are in the range 3001K to 3007K etc

It can be seen that I would like a specified range of values added based on the criteria of account numbers in Col A

I have tried to use the sumproduct formula to show you what I want to achieve, but it gives me an error

Your assistance in this regard is most appreciated

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:76px;" /><col style="width:64px;" /><col style="width:105px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3001</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-137,942.08</td><td > </td><td >3001 to 3007</td><td >#VALUE!</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >3001D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">8,438.68</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >3001K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-9,500.00</td><td > </td><td >3001D to 3007D</td><td >#VALUE!</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3001X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.60</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3002</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-125,080.80</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >3002D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10,607.22</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >3002K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-14,000.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >3002X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3003</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >3003D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >3003K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >3003X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">3004</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-436,752.67</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >3004D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">73,435.76</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >3004K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-63,526.20</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >3004X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.80</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">3005</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >3005D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >3005K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >3005X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">3006</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-102,799.58</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >3006D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2,994.49</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >3006K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-4,000.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >3006X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">3007</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >3007D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >3007K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >3007X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$1:$A$2000)</span>=<span style=' color:008000; '>("3001,3002,3003,3004,3005,3005,3006,3007")</span>,$E$1:$E$2000)</td></tr><tr><td >H3</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$1:$A$2000)</span>=<span style=' color:008000; '>("3001D,3002D,3003D,3004D,3005D,3005D,3006D,3007D")</span>,$E$1:$E$2000)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Akashwani]

Hi,

Try.....

=SUMPRODUCT(--(ISNUMBER($A$1:$A$28)),$E$1:$E$28)

And.....

=SUMPRODUCT(--(ISTEXT($A$1:$A$28)),$E$1:$E$28)

I hope that works.

Ak

 

[Akashwani]

Hi,

Ooops!
Sorry, I've just realised I posted my "test" formulas!!

=SUMPRODUCT(--($A$1:$A$28>=3000)*($A$1:$A$28<=3007),$E$1:$E$28)

=SUMPRODUCT(--($A$1:$A$28>="3001D")*($A$1:$A$28<="3007D"),$E$1:$E$28)

You can edit the above formula to sum 3001K to 3007K just change this.... 3001D

I hope that does it.

Ak

 

[howard]

Thanks for the reply & your help. The formula is not giving me the correct result. I used the sum formula to show you what I want to achieve

Kindly amend your sumproduct formula to give me the desired result

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:76px;" /><col style="width:64px;" /><col style="width:105px;" /><col style="width:76px;" /><col style="width:135px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3001</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-137,942.08</td><td > </td><td >3001 to 3007</td><td style="text-align:right; ">-798121.98</td><td >Using Sumproduct</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >3001D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">8,438.68</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >3001K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-9,500.00</td><td > </td><td >3001D to 3007D</td><td style="text-align:right; ">0</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3001X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.60</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3002</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-125,080.80</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >3002D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10,607.22</td><td > </td><td >3001 to 3007</td><td style="text-align:right; ">-905,374.71</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >3002K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-14,000.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >3002X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td >3001D to 3007D</td><td style="text-align:right; ">95,476.15</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3003</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >3003D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >3003K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >3003X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">3004</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-436,752.67</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >3004D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">73,435.76</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >3004K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-63,526.20</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >3004X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.80</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">3005</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >3005D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >3005K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >3005X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">3006</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-102,799.58</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >3006D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2,994.49</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >3006K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-4,000.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >3006X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">3007</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >3007D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >3007K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >3007X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(ISTEXT<span style=' color:#0000ff; '>($A$1:$A$28)</span>)</span>,$E$1:$E$28)</td></tr><tr><td >H3</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(ISNUMBER<span style=' color:#0000ff; '>($A$1:$A$28)</span>)</span>,$E$1:$E$28)</td></tr><tr><td >H6</td><td >=SUM(E1,E5,E9,E13,E21,E21,E25)</td></tr><tr><td >H8</td><td >=SUM(E2,E6,E14,E22,E26)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[howard]

Thanks for the help. Formula still not giving me desired result-see sample data. Have used SUM formula to show you what i want to achieve

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:76px;" /><col style="width:64px;" /><col style="width:105px;" /><col style="width:76px;" /><col style="width:135px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3001</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-137,942.08</td><td > </td><td >3001 to 3007</td><td style="text-align:right; ">0</td><td >Using Sumproduct</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >3001D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">8,438.68</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >3001K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-9,500.00</td><td > </td><td >3001D to 3007D</td><td style="text-align:right; ">-660179.9</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3001X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.60</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3002</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-125,080.80</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >3002D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10,607.22</td><td > </td><td >3001 to 3007</td><td style="text-align:right; ">-905,374.71</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >3002K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-14,000.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >3002X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td >3001D to 3007D</td><td style="text-align:right; ">95,476.15</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3003</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >3003D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >3003K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >3003X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">3004</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-436,752.67</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >3004D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">73,435.76</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >3004K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-63,526.20</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >3004X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.80</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">3005</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >3005D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >3005K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >3005X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">3006</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-102,799.58</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >3006D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2,994.49</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >3006K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-4,000.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >3006X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">3007</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >3007D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >3007K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >3007X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$1:$A$28>=3000)</span>*<span style=' color:008000; '>($A$1:$A$28<=3007)</span>,$E$1:$E$28)</td></tr><tr><td >H3</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$1:$A$28>="3001D")</span>*<span style=' color:008000; '>($A$1:$A$28<="3007D")</span>,$E$1:$E$28)</td></tr><tr><td >H6</td><td >=SUM(E1,E5,E9,E13,E21,E21,E25)</td></tr><tr><td >H8</td><td >=SUM(E2,E6,E14,E22,E26)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Akashwani]

Hi Howard,

I'm having difficulty constructing the correct formula for H3 as Excel sees 3004K as being between 3001D and 3007D (which it is!)

The best I can come up with is this....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	3001				-137,942.08		3001 to 3007	-802575.13	-905374.71
2	3001D				8,438.68
3	3001K				-9,500.00		3001D to 3007D	95476.15	95476.15
4	3001X				0.6
5	3002				-125,080.80
6	3002D				10,607.22
7	3002K				-14,000.00
8	3002X				0.4
9	3003				0
10	3003D				0
11	3003K				0
12	3003X				0
13	3004				-436,752.67
14	3004D				73,435.76
15	3004K				-63,526.20
16	3004X				1.8
17	3005				0
18	3005D				0
19	3005K				0
20	3005X				0
21	3006				-102,799.58
22	3006D				2,994.49
23	3006K				-4,000.00
24	3006X				0.4
25	3007				0
26	3007D				0
27	3007K				0
28	3007X				0
29
Sheet4

By the way, the reason why our formulas don't match for 3001 to 3007 is that you have counted E21 twice!!

I'm sure we will get a solution, hang in there Howard!

Ak

 

[howard]

Thanks for the reply.

The sumproduct for the account numbers 3001 to 3007 gives me zero. The reason for this that the numbers in col A are text.

The sumproduct as far as 3001D to 3007D is concerned is correct if there is only a range 3001D to 3007D. However if there is a rangge 4001D to 4007D, it will include these. I only want to add the range 3001D to 3007D and once the formula for this range works, I will then set up Sumproduct formulas for the rest of my ranges

If you need my spreadsheet send me a private message & I can then attach my file

Regards

Howard

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:47px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:79px;" /><col style="width:105px;" /><col style="width:76px;" /><col style="width:209px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3001</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-137,942.08</td><td >3001 to 3007</td><td style="text-align:right; ">0</td><td >Using Sumproduct</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >3001D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">8,438.68</td><td > </td><td > </td><td >Not correct as it sees numbers at text</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >3001K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-9,500.00</td><td >3001D to 3007D</td><td style="text-align:right; ">115691.602</td><td >Incorrect, as it included as numbers ending in a D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3001X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.60</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3002</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-125,080.80</td><td >3001 to 3007</td><td style="text-align:right; ">-802,575.13</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >3002D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10,607.22</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >3002K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-14,000.00</td><td >3001D to 3007D</td><td style="text-align:right; ">95,476.15</td><td >Using Sum</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >3002X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3003</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">3004</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-436,752.67</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >3004D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">73,435.76</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >3004K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-63,526.20</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >3004X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.80</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">3005</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >3005D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">3006</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-102,799.58</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >3006D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2,994.49</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >3006K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-4,000.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >3006X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.40</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">3007</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >3007D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >3007K</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >3007X</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >3500D</td><td > </td><td > </td><td > </td><td style="text-align:right; ">20,215.45</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-family:Courier; text-align:right; ">4000</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">89,374.14</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-family:Courier; ">4000A</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">2,402.73</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-family:Courier; text-align:right; ">4001</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">24,988.56</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="font-family:Courier; ">4001A</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">495.78</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="font-family:Courier; text-align:right; ">4002</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">23,105.15</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="font-family:Courier; ">4002A</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">1,748.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="font-family:Courier; text-align:right; ">4004</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">78,067.87</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-family:Courier; ">4004A</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">2,337.94</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="font-family:Courier; text-align:right; ">4006</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">17,483.04</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="font-family:Courier; ">4006A</td><td style="font-family:Courier; "> </td><td > </td><td > </td><td style="font-family:Courier; text-align:right; ">1,766.90</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="text-align:right; ">3501</td><td > </td><td > </td><td > </td><td style="text-align:right; ">25,785.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="text-align:right; ">3515</td><td > </td><td > </td><td > </td><td style="text-align:right; ">182,745.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="font-family:Courier; ">3500A</td><td > </td><td > </td><td > </td><td style="text-align:right; ">35,147.25</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$1:$A$186>=3001)</span>*<span style=' color:008000; '>($A$1:$A$186<=3007)</span>,$E$1:$E$186)</td></tr><tr><td >G3</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(RIGHT<span style=' color:#0000ff; '>($A$1:$A$186,1)</span>="D")</span>,<span style=' color:008000; '>($E$1:$E$186)</span>)</td></tr><tr><td >G5</td><td >=SUM(E1,E5,E9,E10,E14,E16,E20)</td></tr><tr><td >G7</td><td >=SUM(E2,E6,E11,E17,E21)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Akashwani]

Hi Howard,

It would have helped to have known those "numbers" where text!

Try this....

=SUMPRODUCT(--($A$1:$A$28>="3001")*($A$1:$A$28<="3007"),$E$1:$E$28)

I'll look at the other problem later, but if you can't wait any longer for a solution, then start a new post and refer back to this one.

Ak