Using MONTH in a formula

LSI Craig

I'm trying to pull sales by rep, stage and then add a formula that calculates the current month. I've been trying but failing to get the "MONTH" formula baked in but keep getting errors. My current formula is below - how do I get it to grab the current month from the "Expected Close Date" column in my sheet.

=SUMIFS({Sheet - Sales Pipeline Forecast Amt}, {Sheet - Sales Pipeline Sales Rep}, $Labels@row, {Sheet - Sales Pipeline Sales Stage}, [Column4]$43)

I've tried add, deleting spaces, equals, brackets but can't get it to work. I've used the TODAY formula successfully but want it to grab the month.

Any help is appreciated.

Hollie Green

Create a helper column to go along with your Expected Close date with the formula

=IFERROR(MONTH([Expected Close Date]@row),"")

This formula will pull in just the month number then you can reference it in your other formula

=SUMIFS({Sheet - Sales Pipeline Forecast Amt}, {Sheet - Sales Pipeline Sales Rep}, $Labels@row, {Sheet - Sales Pipeline Sales Stage}, [Column4]$43,{helper column},MONTH(TODAY()))

LSI Craig

Hollie,

Thanks for the response. The "helper column" idea was a good one. When I add in the new formula it now returns a valid number but not the correct number. For example, I want it to return anything in the month of May. How do I alter the formula to return a specific month?

Paul Newcome

You don't need a helper column. You can just reference the date column directly.

=SUMIFS({Sheet - Sales Pipeline Forecast Amt}, {Sheet - Sales Pipeline Sales Rep}, $Labels@row, {Sheet - Sales Pipeline Sales Stage}, [Column4]$43, {Source Sheet Date Column}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

If you want to specify a month so that it is not dynamic, you would change MONTH(TODAY()) to whatever number you need.

If you have multiple years on your sheet and want to also specify which year, you would use something more like this:

=SUMIFS({Sheet - Sales Pipeline Forecast Amt}, {Sheet - Sales Pipeline Sales Rep}, $Labels@row, {Sheet - Sales Pipeline Sales Stage}, [Column4]$43, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Same thing goes for the year if you want to make that static.