Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I sum all of the $ payments made between two dates
I have a summary sheet where I want to know show the sum of all invoices paid
per month. In the "invoices" sheet I enter the invoices, the amount, data paid etc. I have tried to use sumif and, for the criteria, used dates as a serial number to select any date range that is of interest. Gives me an error and I'm out of ideas. Andrew |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I sum all of the $ payments made between two dates
Andrew
Assuming you have a column of invoice dates in column A and a column of invoice amounts in column B. First invoice date is in D1 and last invoice date is in E1. The following array formula will sum the invoice amounts between date in D1 and date in E1. =SUMPRODUCT(--(A1:A18=D1),--(A1:A18<=E1),B1:B18) Note that since this is an array formula you need to use Ctrl+Shift+Enter to enter formula. I assumed the array ends at the 18th row but you can change it to whatever you need. Dave "Andrew" wrote in message ... I have a summary sheet where I want to know show the sum of all invoices paid per month. In the "invoices" sheet I enter the invoices, the amount, data paid etc. I have tried to use sumif and, for the criteria, used dates as a serial number to select any date range that is of interest. Gives me an error and I'm out of ideas. Andrew |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I sum all of the $ payments made between two dates
SUMPRODUCT is not an array formula and should just be entered as normal i.e
with Enter. Alternatve solution: =sumproduct(--(Month(a1:a100)=6),B1:b100) will give invoices for June. Obviously only applies if data is for a single year. Otherwise use Dave's formula. HTH "Dave Sheldon" wrote: Andrew Assuming you have a column of invoice dates in column A and a column of invoice amounts in column B. First invoice date is in D1 and last invoice date is in E1. The following array formula will sum the invoice amounts between date in D1 and date in E1. =SUMPRODUCT(--(A1:A18=D1),--(A1:A18<=E1),B1:B18) Note that since this is an array formula you need to use Ctrl+Shift+Enter to enter formula. I assumed the array ends at the 18th row but you can change it to whatever you need. Dave "Andrew" wrote in message ... I have a summary sheet where I want to know show the sum of all invoices paid per month. In the "invoices" sheet I enter the invoices, the amount, data paid etc. I have tried to use sumif and, for the criteria, used dates as a serial number to select any date range that is of interest. Gives me an error and I'm out of ideas. Andrew |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I sum all of the $ payments made between two dates
Can anyone help me do this when the range is across worksheets? I have a
worksheet for each month, each row in an invoice with the amount in column I and the date payment was posted in column M. I want to total the payments made by date posted, so they could be on previous worksheets. "Andrew" wrote: I have a summary sheet where I want to know show the sum of all invoices paid per month. In the "invoices" sheet I enter the invoices, the amount, data paid etc. I have tried to use sumif and, for the criteria, used dates as a serial number to select any date range that is of interest. Gives me an error and I'm out of ideas. Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart formatting with dates | Charts and Charting in Excel | |||
formula to add dates. | Excel Worksheet Functions | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Charts/ Graphs and Dates Trouble | Charts and Charting in Excel | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |