Sheet Summary Field for Monthly Revenue Totals

I created the following sheet summary formula in an effort to total forecasted revenue for jobs that are scheduled to run during the month of July:

=SUMIF([Scheduled Start]:[Scheduled Start], MONTH(@cell) = 7, ([Projected Revenue]:[Projected Revenue]))

Unfortunately, the above returns #INVALID DATA TYPE. Can anyone tell me what I'm doing wrong?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =SUMIF([Scheduled Start]:[Scheduled Start], AND(ISDATE(@cell), MONTH(@cell) = 7), [Projected Revenue]:[Projected Revenue])

    [Scheduled start] must be a date column. [Projected revenue] must be a number. The column with this formula must be text/number.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thanks, @Mark Cronk, but unfortunately it did not.

    The columns are date and text/number types and I reviewed the contents of the columns to double-check that they only have dates or dollar amounts as appropriate. I'm still getting an #INVALID DATA TYPE error.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Katherine,

    I suggest checking your column contents again. Look for anything in [Scheduled Start] that's not a date. Look for anything that's not a number in [Projected Revenue].

    Is [Projected Revenue] a calculation? If it is try wrapping it in =VALUE(.....)

    If it still doesn't work, add a temporary number column next to [Scheduled start] with the formula =MONTH([scheduled start]@row). Each cell should be a number between 1 and 12. Look for an error. That's your problem. If that doesn't result in an error do the same thing next to [projected revenue] with the formula = 2*[projected revenue]@row). Look for an error.

    Cross your fingers.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hi Mark -

    I figured it out. It just needed an IFERROR and now it works perfectly:

    =SUMIF([Scheduled Start]:[Scheduled Start], (IFERROR(MONTH(@cell), 0) = 7), [Projected Revenue]:[Projected Revenue])

    Thanks!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Perfect. Glad you found a solution. Well done. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!