Help with Formula Please
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.
Can anyone help please?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!