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


Answers

  • Jason Albrecht
    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.

    Hope this helps and that you have a great day,

    Jason

  • kstaver
    kstaver ✭✭✭

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

  • Jason Albrecht
    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.

    Hope this helps and that you have a great day,

    Jason

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!