sum dollars between date ranges

How do I sum dollars between 2 date ranges?

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

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Samuel Dowdy Jr.
    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
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Samuel,

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Samuel Dowdy Jr.
    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
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!