Hi,

I am trying to find the formula so that the results show the total combined forecast amount but excludes any forecasted amount for an expected close date of 2025.

Tags:

• ✭✭✭✭✭✭

As you've some blank dates in there, you can't just use a SUMIF directly, so go with a formula which SUMs all the Forecast Amount and then subtracts the known 2025 values:

=SUM([Forecast Amount]:[Forecast Amount]) - SUMIF([Expected Close Date]:[Expected Close Date], 2025, [Forecast Amount]:[Forecast Amount])

Hope this helps, but if you've any problems/questions then just ask!

• Thanks @Nick Korna - should have mentioned that this is going into a different sheet so how to I reference those collumns?

• ✭✭✭✭✭✭

Substitute the columns in the formulas with the cross sheet references. If you're unfamiliar with how to do cross sheet references, then here are a couple of help articles (one text, one with video):

If you rename the cross sheet references to their respective columns you should end up with something like this:

=SUM({Forecast Amount}) - SUMIF({Expected Close Date}, @cell = 2025, {Forecast Amount})

In the above formula the { } indicates the column in your data sheet the cross sheet reference is pointing at.

If you need any more help on this, I can do some step by step screen shots though the above should hopefully be enough. 🙂

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!