Sum Ifs formula to calculate everything in a certain stage each month IF that month is in the future
Hi. So this is the formula I currently have: =SUMIFS([month #]66:[month #]77, >=MONTH(TODAY()), Client66:Client77, Stage:Stage, "Stage 1 Prospecting")
The month # is 1-12 on each row for each monthly calculation of TCV , and then the Client is the actual monthly calculation. I am getting an invalid operation error. What I am trying to sum is the monthly calculation for the TCV, which is already calculated, IF the opportunity is in stage 1and the month is in the future. Please help :)
Answers
-
Hi @Chandlerw
The first thing is that you have three ranges listed in the formula, but two of them are set to 11 rows whereas the other range is set to the whole sheet.
Ex:
Client66:Client77 looks from row 66 to row 77
Stage:Stage looks at the entire sheet in that column
You'll need to have consistent column ranges in your formula. Create a Cell or Column Reference in a Formula
The second thing is the way that a SUMIFS works is that you first list the column you want to SUM (your monthly calculation for the TCV column) then list each range and its criteria together to define what Rows to filter down, like so:
=SUMIFS([SUM Column]:[SUM column], [column 1]:[column 1], "criteria 1", [column 2]:[column 2], "criteria 2", [column 3]:[column 3], "criteria 3")
In your case, the second Column is missing criteria (what Client are you looking for?), and there's no column to identify what you want to SUM.
Try something like this:
=SUMIFS([Sum Column]:[Sum Column], [month #]:[month #], @cell >=MONTH(TODAY()), Client:Client, "Client 1", Stage:Stage, "Stage 1 Prospecting")
If I've misunderstood what you're looking to do, it would help to see a screen capture of your sheet but please block out any sensitive data.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!