Formula to calculate revenue by date

Amy Shank · 06/01/23

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.

Amy Shank · 06/02/23

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

Kleerfyre · 06/01/23

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)

Amy Shank · 06/02/23

That formula sample is coming back as unparseable.

Darren Mullen · 06/02/23

@Amy Shank Small correction to @Kleerfyre formula:

=SUMIFS([Payment Amount]:[Payment Amount],Created:Created, MONTH(@cell)=1)

Kleerfyre · 06/02/23

My bad on the formula error. I sometimes have to play with them until I get the correct outcome.

Darren Mullen · 06/02/23

@Kleerfyre Me too. It happens :)

Amy Shank · 06/02/23

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

Best Answer