Formula to calculate revenue by date
I would like to write a formula to calculate revenue by month using the Created date.
I have tried the formula below and it's invalid operation.
=SUMIFS([Payment Amount]:[Payment Amount], AND(MONTH(@cell) = 1), Created:Created)
The date format in the created column is 05/04/23 1:09 PM.
Best Answer
-
Thank you both for the timely response! I love this community! I was able to get the following to work:
=SUMIFS([Payment Amount]:[Payment Amount], Created:Created, AND(@cell >= DATE(2023, 5, 1), @cell <= DATE(2023, 5, 31)))
Answers
-
You don't need the AND statement in a SUMIFS formula. You also need a criteria for the Created column.
Is the AND what you are trying to use to pull the month out of the date? If so, your formula would look more like this:
=SUMIFS([Payment Amount]:[Payment Amount],Created:Created, MONTH(Created@row)=1)
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
That formula sample is coming back as unparseable.
-
@Amy Shank Small correction to @Kleerfyre formula:
=SUMIFS([Payment Amount]:[Payment Amount],Created:Created, MONTH(@cell)=1)
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
My bad on the formula error. I sometimes have to play with them until I get the correct outcome.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@Kleerfyre Me too. It happens :)
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Thank you both for the timely response! I love this community! I was able to get the following to work:
=SUMIFS([Payment Amount]:[Payment Amount], Created:Created, AND(@cell >= DATE(2023, 5, 1), @cell <= DATE(2023, 5, 31)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!