Excel, how to Sum on Column based on if the dates in another Column match the current month?

Question

I need to find the sum of the cells in a Column if their end dates in another column occur in the current month.

I tried using the formula =sumif(L:L,MONTH(TODAY()),J:J) but it doesn't work.

Column L contains the full date (ex: 12/1/18).

Column J contains the values I want to add up.

So since it's December, I want to add all the values in J that have a date that is in December, and so on a so forth when I use the sheet in January.

What does month(today()) evaluate to? Does it match what you are comparing it to in column J? if it doesn't, what do you need to do? — Solar Mike
I need MONTH(TODAY()) to equal to the current Month, so for today it should equal December, but when I open and use this sheet again in January, it will equal January — bern nick
Month(today()) evaluates to a number (12 when I tried it just now), but if you are comparing it to a full date 12/1/18 how do you expect it to match? — Solar Mike

Lisa Lisa · Accepted Answer · 2018-12-05T18:33:18

Try this formula:

=SUM(IF(MONTH(L:L)=MONTH(TODAY()),J:J,0))

and enter it with Ctrl-Shift-Enter as it is an array formula.

Or use =SUMPRODUCT((J:J)*(MONTH(L:L)=MONTH(TODAY())))