subtotal IF

    Is there such a thing as subtotal if???
    I am using arrays all over my worksheet and it is really slowing the calculations down. I need to subtotal a column of numbers after filtering off on a criteria from one column but only summing those numbers based on another criteria from another column. It would be great if i could use subtotal IF or maybe subtotal(sumif(#,#)). Does this sound possible??

    hey, I ran accrross this problem a while ago.....


    much as it pains me to say it, but, nope, I don't think it's possible


    in the end, I resorted to an archaic method of extended my IFs along the columns to the right and then inserting
    =SUBTOTAL(9, etc etc functions at the top of those columns to catch the summing when it was filtered


    Hopefully, this will bounce it out there to see if anyone else has a method, maybe via VBA (?)


    I'd love to see a solution as I'd use it tommorrow, but as yet (2 years after I encountered the problem) I haven't seen a solution


    :(


    Hope this helps, albeit negatively
    Chris

    maybe I spoke too quickly....


    it's certainly possible to get a sum of results based on more than a single criterion.... ie 2 criteria....


    see the foolowing example :


    <CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=7 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - Book2</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=7&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb4287262003'&gt;<INPUT TYPE='Button' NAME='btCb5793506491' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb7224230428.sltNb9631429103.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=7&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb7224230428'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb9631429103' onChange='document.formFb7224230428.txbFb5954886793.value = document.formFb7224230428.sltNb9631429103.value'&gt;<option value='=SUMPRODUCT((A2:A25="George")*(B2:B25<F5),(B2:B25))'&gt;E7</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb5954886793' size='120' value='=SUMPRODUCT((A2:A25="George")*(B2:B25<F5),(B2:B25))'&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;E</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;F</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;name</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;amount</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;1</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;2</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;3</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Ist criteria</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;equal to</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;George</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;4</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;2nd criteria</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;less than</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;6</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;formula</FONT&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=SUMPRODUCT((A2:A25=%22George%22)*(B2:B25%3CF5),(B2:B25))')&gt;<FONT FACE=Arial COLOR=#000000&gt;9</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;7</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;9</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;8</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;10</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;9</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;11</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;8</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;12</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;7</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;13</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;6</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;14</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;15</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;4</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;16</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;3</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;17</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;2</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;18</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;1</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;19</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;2</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;20</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;3</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;21</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;4</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;22</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;john</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;23</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;paul</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;6</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;24</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;george</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;7</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;25</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;ringo</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;8</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=7&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker 2.0@BETA]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;


    where you are asking for all sum of records for beatle "George" where the amount is less than 5....


    bringing the info back via =SUBTOTAL(9 etc etc on purely filtered info though, that's where I hit a brick wall...

    Thanks for the input,
    I tried the sumproduct and it doesn't seem to speed up the calculations compared to the arrays. Hopefully something will come up to where we can use the subtotal statement in this fasion.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login