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

Rudy Renteria

Nic Larsen

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

Nic Larsen

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

Nic Larsen

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)

Rudy Renteria

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?

Nic Larsen

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

Rudy Renteria

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

Nic Larsen

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.

Nic Larsen

Give this one a try:

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

Rudy Renteria

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

Rudy Renteria

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.

Nic Larsen

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

Rudy Renteria

It worked, Thank you!