Sheet Summary Field for Monthly Revenue Totals

Katherine Wilgruber

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?

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

Katherine Wilgruber

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

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

Katherine Wilgruber

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

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

Mark