IF formulas to generate month from a date column

I am having trouble with a SUMIFS formula. I want to calculate the revenue for sales in August 2023. I want to add a chart to a dashboard that shows the revenue for each month, and have the months appear in chronological order.

I have a Revenue Column, A Ship Date Column (we use this to help manage "end of month"), and then a Month column.

Here is my formula: =SUMIFS(Revenue:Revenue, [Ship Date:Ship Date],CONTAINS("2023"@cell), [Month]:Month], CONTAINS("August"@cell)

(I did create a report and then grouped the report by month with revenue as a summary, but when I go to create a chart in a dashboard I can't rearrange the months so they appear in chronological order, they appear in alphabetical order. I tried to sort the report by date, but the month grouping didn't change.)

Find more posts tagged with

Accepted answers

All comments

Jason Albrecht

Hi @kstaver,

Would this work for you?

=SUMIFS(Revenue:Revenue, [Order Date]:[Order Date], YEAR(@cell) = YEAR(TODAY()), [Order Date]:[Order Date], MONTH(@cell) = 8)

The last number in the formula relates to the month. In this case, 8 = August. Simply change the number for the month you're looking for.

kstaver

I am getting a #invalid data type error code when I try this formula.

Jason Albrecht

Hi @kstaver

Where is the formula? I hope it's not in the revenue column?
Check that the column names don't have spaces (e.g. you want "Revenue", not "Revenue ")
Check the data in the Revenue and Date column (e.g. only numbers and dates, nothing else for the whole column)

As you can see from this Smartsheet Help on Error Codes, the #INVALID DATA TYPE contains or references an incompatible data type.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.