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
-
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, 2criterion2... ])
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I couldn't get it to work, I'm having to reference another page for the invoice amount and the invoice date
-
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.
-
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.
-
I never found a solution. Im trying to sum a dollar amount on another sheet if the dates are in january
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!