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

#### howard

##### Well-known Member
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Howard.

Is this what you mean?....

Excel Workbook
BCDEFGHIJK
1Acc #ValueLen CheckFormula1Formula2
23000-30151009200200
33000-3015A5010
43000-30151009
53000-3015A5010
63000A-30155010
73000A-3015A5011
8
Sheet4

I hope that helps.

Ak

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

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

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

Last edited:
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

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

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
ABCDEFGHIJ
13001-137,942.083001 to 3007-802575.13-905374.71
23001D8,438.68
33001K-9,500.003001D to 3007D95476.1595476.15
43001X0.6
53002-125,080.80
63002D10,607.22
73002K-14,000.00
83002X0.4
930030
103003D0
113003K0
123003X0
133004-436,752.67
143004D73,435.76
153004K-63,526.20
163004X1.8
1730050
183005D0
193005K0
203005X0
213006-102,799.58
223006D2,994.49
233006K-4,000.00
243006X0.4
2530070
263007D0
273007K0
283007X0
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

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

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

Replies
6
Views
255
Replies
6
Views
437
Replies
0
Views
284
Replies
1
Views
600
Replies
0
Views
275

1,203,174
Messages
6,053,929
Members
444,694
Latest member
JacquiDaly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back