How to get the total?

gigiyi

Board Regular
Joined
May 11, 2009
Messages
205
<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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
=SUMIF(A2:A13,"<="&D2,B2:B13)

Assuming you have a date in D2.

Edit: added missing quotes, sorry.
 
Upvote 0
We don't solve riddles, we solve excel problems.

Please elaborate on what you want...

EDIT: Never mind, I guess we do solve riddles. :D
 
Upvote 0
Thank you Zack,

I got it.

May I ask another question?
If I want to add up the total between 2/3/09 to 2/5/2009, how can I do it?
Please help.

gigi
 
Upvote 0
Sure gigi, use another SUMIF()...

=SUMIF(A2:A13,"<="&D3,B2:B13)-SUMIF(A2:A13,"<="&D2,B2:B13)

Assuming D3 houses your latter date and D2 houses the first date.
 
Upvote 0
Another approaching,

=SUMPRODUCT((A2:A13<=D3)-(A2:A13<=D2),B2:B13)

Regards
Bosco
 
Upvote 0
gigiyi,

Excel Workbook
ABCDE
1DATETOTALcriteriaTOTAL
22/1/200952/6/200970
32/2/200910
42/3/200920
52/4/200925
62/5/20095
72/6/20095
82/7/20096
92/8/200910
102/9/200915
112/10/200960
122/11/200930
132/12/200945
14
Sheet1
 
Upvote 0
OK so here is another example of part of the code/formula that I would like to understand why it is there and what it does (did one earlier today too)

Not having used sumproduct much, what is the "--" for. I think I get the rest just fine.

Thanks
ASM
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top