What is the formula I need to calculate the amount sum of a column for the past month? Then week?
Best Answers
-
Try it like this:
=SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()
-
Try wrapping entire formula in an IFERROR. You may need to manually type entire formula too. Sometimes the copy and paste can cause problems.
=IFERROR(SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, IFERROR(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, 0)), "")
Answers
-
To sum the month by column would look something like this. In this case, it is looking at Month of January. Change the 1 to 2 and so on to sum by each month.
=SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, IFERROR(MONTH(@cell), 0) = 1)
-
What do you add to the formula to always give you the previous month's sum, without changing it to a (1) or a (2) manually?
-
Try it like this:
=SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()
-
It worked! Could you provide a formula to give you the past week's data?
-
Glad that worked.
Prior Week - that I've not tried before, but I'll see what I can come up with.
Perhaps another community member has done this and can chime in.
-
Give this one a try:
=SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, IFERROR(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, 0))
-
Understood, could you explain what the argument inside each criteria means? Maybe I can help myself based on the explanations provided.
-
The week formula gave me back an "UNPARSEABLE" result. I even added a new sum range column to convert the date to a "weeknumber" and it still didn't work.
-
Try wrapping entire formula in an IFERROR. You may need to manually type entire formula too. Sometimes the copy and paste can cause problems.
=IFERROR(SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, IFERROR(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, 0)), "")
-
It worked, Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!