sum dollars between date ranges

Samuel Dowdy Jr.

How do I sum dollars between 2 date ranges?

sum = invoice amount if date on invoice is greater than date and less another date

Mark Cronk

Hi @Samuel Dowdy Jr ,

You'll use a SUMIF function. The syntax is: SUMIF( range, criterion, [ sum_range ])

You'll need to use your column names but your formula should look like:

=SUMIF([Invoice Date:[Invoice Date], AND(@cell < DATE(2020, 12, 31), @cell > DATE(2020, 1, 1)), [Invoice Amt]:Invoice Amt])

You'll need to use SUMIFS if you have more criteria. The SUMIFS syntax is:

SUMIFS( range, criterion_range1, criterion1, [ criterion_range, 2​criterion2​... ])

Work for you?

Mark

Samuel Dowdy Jr.

I couldn't get it to work, I'm having to reference another page for the invoice amount and the invoice date

Mark Cronk

Hi @Samuel Dowdy Jr ,

To reference another sheet you'll replace the Invoice Date and Invoice Amt ranges in [ ]:[ ] with external references that will be in { } . Place the cursor in the formula where you want the external range. A blue Reference Another Sheet link will appear. Click it, find the sheet you need and select the range.

Your range names may be different but your formula should look like this:

=SUMIF({Invoice Date range}, AND(@cell < DATE(2020, 12, 31), @cell > DATE(2020, 1, 1)), {Invoice Amt Range})

Help?

Mark

Mark Cronk

Hi Samuel,

Did you find a solution? If so, please accept an answer to close the discussion.

Mark

Samuel Dowdy Jr.

I never found a solution. Im trying to sum a dollar amount on another sheet if the dates are in january

Mark Cronk

Hi @Samuel Dowdy Jr ,

=SUMIF({Invoice Date range}, MONTH(@cell)=1, {Invoice Amt Range})

Replace {Invoice Date Range} with the external link to the invoice date column on your other sheet. Replace {invoice amount} with the external link to the invoice amounts on your other sheet.

Work this time?

Mark