# What is the formula I need to calculate the amount sum of a column for the past month? Then week?

Options
edited 02/15/24

• ✭✭✭✭✭✭
Options

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()

• ✭✭✭✭✭✭
Options

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)), "")

• ✭✭✭✭✭✭
Options

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)

• Options

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?

• ✭✭✭✭✭✭
Options

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()

• Options

It worked! Could you provide a formula to give you the past week's data?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Give this one a try:

=SUMIFS([MP OP Daniel]:[MP OP Daniel], Date:Date, IFERROR(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, 0))

• Options

Understood, could you explain what the argument inside each criteria means? Maybe I can help myself based on the explanations provided.

• Options

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.

• ✭✭✭✭✭✭
Options

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)), "")

• Options

It worked, Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!