How to get the total?

[gigiyi]

<table x:str="" style="border-collapse: collapse; width: 434px; height: 182px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 56pt;" width="74"> <col style="width: 48pt;" width="64"> <col style="width: 123pt;" width="164"> <col style="width: 47pt;" width="63"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt; width: 48pt;" width="64" height="17">A</td> <td class="xl29" style="width: 56pt;" width="74">B</td> <td class="xl29" style="width: 48pt;" width="64">
</td> <td class="xl29" style="width: 123pt;" width="164">D</td> <td class="xl29" style="width: 47pt;" width="63">E</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">DATE</td> <td class="xl27" style="border-left: medium none;">TOTAL</td> <td>
</td> <td class="xl30">criteria</td> <td class="xl30" style="border-left: medium none;">TOTAL</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39845" align="right" height="17">2/1/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">5</td> <td>
</td> <td class="xl26" style="border-top: medium none;"><= 2/6/2009</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="">70</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39846" align="right" height="17">2/2/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">10</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39847" align="right" height="17">2/3/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">20</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39848" align="right" height="17">2/4/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">25</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39849" align="right" height="17">2/5/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">5</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39850" align="right" height="17">2/6/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">5</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39851" align="right" height="17">2/7/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">6</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39852" align="right" height="17">2/8/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">10</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39853" align="right" height="17">2/9/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">15</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39854" align="right" height="17">2/10/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">60</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39855" align="right" height="17">2/11/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">30</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="border-top: medium none; height: 12.75pt;" x:num="39856" align="right" height="17">2/12/2009</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="" align="right">45</td> <td>
</td> <td>
</td> <td class="xl24">
</td> </tr> </tbody></table>

 

[hiker95]

AusSteelMan,

There are a lot of ways to use SUMPRODUCT.

Try:

SUMPRODUCT with * can fail sometimes.
http://www.mrexcel.com/forum/showthread.php?t=385842

SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

SUMPRODUCT white paper
http://www.mrexcel.com/forum/showthread.php?t=367660
http://www.mrexcel.com/wwwboard/messages/8961.html
http://www.emailoffice.com/excel/arrays-bobumlas.html
http://j-walk.com/ss/excel/eee/index.htm

 

[AusSteelMan]

Thanks hiker95.

Reading just the first link helped heaps. I will continue reading up and give it a go in a test.

Great resource.
Thanks,
ASM

 

[Zack Barresse]

Also, Bob Phillips has his own dedicated forum to SUMPRODUCT() help here...

http://www.vbaexpress.com/forum/forumdisplay.php?f=98

I would use the SUMIF() in this instance as I doubt you'd beat the efficiency with SUMPRODUCT(), IMHO.

 

[Beachson]

I beilieve you can simply open a pivot table to acquire any of these totals by any category you have set

 

[Zack Barresse]

True. Or DSUM(), or even Advanced Filter would work.