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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
=SUMIF(A2:A13,"<="&D2,B2:B13)

Assuming you have a date in D2.

Edit: added missing quotes, sorry.
 

lionpants

Board Regular
Joined
Nov 11, 2009
Messages
117
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
 

gigiyi

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

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Another approaching,

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

Regards
Bosco
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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
 

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,344
Messages
5,635,751
Members
416,877
Latest member
SolarTheory

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
Top