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
Check out the Formula Handbook template!