Formula to calculate revenue by date

Amy Shank

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.

Find more posts tagged with

Accepted answers

Amy Shank

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

All comments

Kleerfyre

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

That formula sample is coming back as unparseable.

Darren Mullen

@Amy Shank Small correction to @Kleerfyre formula:

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

Kleerfyre

https://community.smartsheet.com/discussion/comment/378588#Comment_378588

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

Darren Mullen

@Kleerfyre Me too. It happens :)

Amy Shank

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.