I have two columns, H and J. Column H contains a date formatted as YYYY-MM-DD. Column J contain a currency with come entries as blank(or null). I’ve tried variations of the condition similar to =SUMIF(H8:H11, “MONTH()=4”, J8:J11), where the 4 represents April, but I always get a sum of 0. Including trying “MONTH()=04” or “MONTH=4”. I am trying to sum the currency total for a particular month. If I can do it for one month, then I can determine the totals for each month or using a similar method for a year.
Hello
try
-
=SUMPRODUCT((MONTH(H8:H11)=4)*(J8:J11))–or– -
=SUMPRODUCT(SUMIF(MONTH(H8:H11);4;J8:J11))–or– -
{=SUMIF(MONTH(H8:H11);4;J8:J11)}(to be entered usingCTRL+SHIFT+ENTER)
Hope that helps
Try
{=SUMIF(MONTH(H8:H11);4;J8:J11)}
This is an array function, the entry of the formula should be completed by pressing Ctrl+Shift+Enter
You can also try
=SUMPRODUCT(MONTH(H8:H11)=4;J8:J11)
For compatibility with Excel, this formula should be written a little more complicated
=SUMPRODUCT(N(MONTH(H8:H11)=4);J8:J11)Thanks much! The first solution worked great for the month where you have ‘4’ is 1 thru 11. When it is 12, then the result is way to big a number. Would you have any idea why that would be?
Are we talking about four cells? Or have you taken on a much larger range of data? Perhaps you have two or more Decembers in your data? For example December 2019 and December 2020? The formula sums up the data for any (all available) Decembers.
=SUMPRODUCT(N(MONTH(H8:H11)=4);J8:J11)
I suppose it should be
=SUMPRODUCT(N(MONTH(H8:H11))=4;J8:J11)No, N() is needed to convert a Boolean value to a number - Excel does not want to understand the simple truth “FALSE = 0” 
Ah - thanks!
My data is a long column with dates from Jan thru Dec of the same year. For me to present the problem, I simplified it to an example with 4 cells. Once implementing the presented solution, I was able to make the sumif… for all months work except the problem I found with the 12 (or December). I could only resolve by typing the formula for December into the formula bar, so the problem is resolved. Interesting enough. I could not copy a working formula from the working formulas from any of the months into the December sumif formula and change the condition to 12. Even copying a blank cell to the December formula cell before copying a formula failed. Thanks for the help, information, and the extra regarding SUMPRODUCT.
I think I get it! Are you talking about the option with array formulas? Yes, editing such cells differs from the usual one (this is probably why array formulas are not popular and are being replaced everywhere by SUMPRODUCT())