What function do i use to total amounts from rows with items from certain dates?

brendongl

New Member
Joined
Jun 16, 2011
Messages
46
Hi

I'm making a cashflow-style spreadsheed in Excel2010 on Windows 7.
I have a couple of sheets, one for summarys, daily sales, invoices, etc.

On my invoices sheet I have date,payee,reference no, subtotal, total incl. gst, etc.

I want to be able to make a function that can add the TOTAL for a certain period of time E.G per month.
I think something like a lookup function would be used here but I'm not very advanced. Any help would be great, if more information is needed please ask. thank you.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
If your dates are in column A and the values you need summed are in column B you can use one of these:

Manually change the start or end date:

=SUMIFS(B:B,A:A,">="&DATEVALUE("05/01/2011"),A:A,"<="&DATEVALUE("05/31/2011"))

Just enter the month number*:

=SUM(IF(MONTH($A:$A)=5,$B:$B,0))


* Make sure you press CTRL+SHIFT+Enter for this one.
 
Upvote 0
Hi,

You can also add total for each month change by subtotal.

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 68px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 85px;"> <col style="width: 64px;"> <col style="width: 99px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>date</td> <td>payee</td> <td>ref no</td> <td>total income</td> <td>GST</td> <td style="font-weight: bold;">Helper column</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">1/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">697</td> <td>
</td> <td style="font-weight: bold; text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">1/25/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">498</td> <td>
</td> <td style="font-weight: bold; text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">1/30/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">819</td> <td>
</td> <td style="font-weight: bold; text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">2/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">602</td> <td>
</td> <td style="font-weight: bold; text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">2/15/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">285</td> <td>
</td> <td style="font-weight: bold; text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">2/28/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">74</td> <td>
</td> <td style="font-weight: bold; text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">3/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">479</td> <td>
</td> <td style="font-weight: bold; text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="text-align: right;">3/10/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">203</td> <td>
</td> <td style="font-weight: bold; text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: right;">3/30/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">521</td> <td>
</td> <td style="font-weight: bold; text-align: right;">3</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F2</td> <td>=MONTH(A2)</td></tr> <tr> <td>F3</td> <td>=MONTH(A3)</td></tr> <tr> <td>F4</td> <td>=MONTH(A4)</td></tr> <tr> <td>F5</td> <td>=MONTH(A5)</td></tr> <tr> <td>F6</td> <td>=MONTH(A6)</td></tr> <tr> <td>F7</td> <td>=MONTH(A7)</td></tr> <tr> <td>F8</td> <td>=MONTH(A8)</td></tr> <tr> <td>F9</td> <td>=MONTH(A9)</td></tr> <tr> <td>F10</td> <td>=MONTH(A10)</td></tr></tbody></table></td></tr></tbody></table> create helper column&extract month using month function.

use subtotal function alt+d+b

with each change in helper column being selected &suming total income or any column you wish to add up.

Output

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 68px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 85px;"> <col style="width: 64px;"> <col style="width: 99px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>date</td> <td>payee</td> <td>ref no</td> <td>total income</td> <td>GST</td> <td style="font-weight: bold;">Helper column</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">1/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">697</td> <td>
</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">1/25/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">498</td> <td>
</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">1/30/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">819</td> <td>
</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">2014</td> <td>
</td> <td style="font-weight: bold;">1 Total</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">2/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">602</td> <td>
</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">2/15/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">285</td> <td>
</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">2/28/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">74</td> <td>
</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">961</td> <td>
</td> <td style="font-weight: bold;">2 Total</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: right;">3/1/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">479</td> <td>
</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">3/10/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">203</td> <td>
</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="text-align: right;">3/30/2011</td> <td>
</td> <td>
</td> <td style="text-align: right;">521</td> <td>
</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">1203</td> <td>
</td> <td style="font-weight: bold;">3 Total</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">4178</td> <td>
</td> <td style="font-weight: bold;">Grand Total</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F2</td> <td>=MONTH(A2)</td></tr> <tr> <td>F3</td> <td>=MONTH(A3)</td></tr> <tr> <td>F4</td> <td>=MONTH(A4)</td></tr> <tr> <td>D5</td> <td>=SUBTOTAL(9,D2:D4)</td></tr> <tr> <td>F6</td> <td>=MONTH(A6)</td></tr> <tr> <td>F7</td> <td>=MONTH(A7)</td></tr> <tr> <td>F8</td> <td>=MONTH(A8)</td></tr> <tr> <td>D9</td> <td>=SUBTOTAL(9,D6:D8)</td></tr> <tr> <td>F10</td> <td>=MONTH(A10)</td></tr> <tr> <td>F11</td> <td>=MONTH(A11)</td></tr> <tr> <td>F12</td> <td>=MONTH(A12)</td></tr> <tr> <td>D13</td> <td>=SUBTOTAL(9,D10:D12)</td></tr> <tr> <td>D14</td> <td>=SUBTOTAL(9,D2:D12)</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
If your dates are in column A and the values you need summed are in column B you can use one of these:

Manually change the start or end date:

=SUMIFS(B:B,A:A,">="&DATEVALUE("05/01/2011"),A:A,"<="&DATEVALUE("05/31/2011"))

Just enter the month number*:

=SUM(IF(MONTH($A:$A)=5,$B:$B,0))


* Make sure you press CTRL+SHIFT+Enter for this one.

The dates are Column A and the amounts are in Column G.

=SUMIFS(A:A,G:G,">="&DATEVALUE("01/01/2010"),A:A,"<="&DATEVALUE("05/12/2011"))

in a random cell should work by suming all the amounts from 1/1/10 to /5/12/11 right? it's returning 0. :confused:
 
Upvote 0
Hi

I'm making a cashflow-style spreadsheed in Excel2010 on Windows 7.
I have a couple of sheets, one for summarys, daily sales, invoices, etc.

On my invoices sheet I have date,payee,reference no, subtotal, total incl. gst, etc.

I want to be able to make a function that can add the TOTAL for a certain period of time E.G per month.
I think something like a lookup function would be used here but I'm not very advanced. Any help would be great, if more information is needed please ask. thank you.
Try this...

Book1
ABCDEF
28/7/201170_1/1/20111/31/2011203
31/8/201143____
44/26/201147____
51/1/201187____
612/31/201134____
79/28/201184____
81/30/20117____
910/24/201112____
104/6/201168____
113/2/201193____
121/13/201152____
136/5/201155____
141/31/20112____
158/21/201146____
161/18/20115____
177/6/201126____
181/22/20117____
194/4/201120____
204/23/201149____
Sheet1

Formula entered in F2...

If you're using Excel 2007 or later:

=SUMIFS(B2:B20,A2:A20,">="&D2,A2:A20,"<="&E2)

This one will work in ANY version of Excel:

=SUMIF(A2:A20,">="&D2,B2:B20)-SUMIF(A2:A20,">"&E2,B2:B20)
 
Upvote 0
None of these have really worked other than the Helper column one...
I'm sure there is a straght forward formula,


I think this would work but it's not for some reason.
My Dates are Column A, Amounts are in column G, it starts from row 3.

=SUMIFS(A:A,G:G,">="&DATEVALUE("01/01/2010"),A:A,"<="&DATEVALUE("05/12/2011"))

Why doesn't this one work?
 
Upvote 0
None of these have really worked other than the Helper column one...
I'm sure there is a straght forward formula,


I think this would work but it's not for some reason.
My Dates are Column A, Amounts are in column G, it starts from row 3.

=SUMIFS(A:A,G:G,">="&DATEVALUE("01/01/2010"),A:A,"<="&DATEVALUE("05/12/2011"))

Why doesn't this one work?
You have the sum range in the wrong location.

It's better to use cells to hold the date criteria:
  • X1 = 1/1/2010
  • Y1 = 5/12/2011
=SUMIFS(G:G,A:A,">="&X1,A:A,"<="&Y1)
 
Upvote 0
Ahh, easiest way I have found is just to create a helper column then use =SUMIF(J:J,5,G:G), 5 representing the month number.

Thanks everyone for their help!
 
Upvote 0
You have the sum range in the wrong location.

It's better to use cells to hold the date criteria:
  • X1 = 1/1/2010
  • Y1 = 5/12/2011
=SUMIFS(G:G,A:A,">="&X1,A:A,"<="&Y1)

Ahh, this works too. Works better as well so i can be more specific than months, since my summary totals are also for weeks. thanks!
 
Upvote 0

Forum statistics

Threads
1,224,606
Messages
6,179,865
Members
452,948
Latest member
UsmanAli786

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