Sum Ifs formula to calculate everything in a certain stage each month IF that month is in the future

Chandlerw

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 :)

Genevieve P.

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