how do I sum all of the $ payments made between two dates

[Guest]

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

 

[Dave Sheldon]

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

 

[Guest]

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

 

[brenadine]

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.