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 112 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!