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

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" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>