Forum Discussion
Formula help - why doesn't this work for January dates please?
Hi, I was kindly given this formula to count when a month occurs across two columns. For some reason it comes up with a random total for the month of January, and I don't know how to resolve it. the formula is:
=SUMPRODUCT(--( (MONTH('Andy''s Team'!E2:E32)=12) + (MONTH('Andy''s Team'!H2:H32)=12) > 0))
(NB ‘Andy's team’ is the tab it's taking the data from)
2 Replies
A few things can cause MONTH() to return unexpected results, but there is one very common cause specifically for January.
The formula only works correctly if every value in the two ranges is a real Excel date.
If any cell contains:- text that looks like a date,
- a blank that Excel interprets as 0,
- a zero value,
- or anything non-date,
then:
MONTH(0) returns 1
…which Excel interprets as January.
So any blank or invalid date will be counted as January — giving you an incorrect total.
In one formula:
=SUMPRODUCT((ISNUMBER('Andy''s Team'!E2:E32))*(MONTH('Andy''s Team'!E2:E32)=12)
+(ISNUMBER('Andy''s Team'!H2:H32))*(MONTH('Andy''s Team'!H2:H32)=12))
This counts only real dates and ignores blanks/text.
I hope it helps them.
Please clarify what this odd formula is trying to achieve.
Both MONTH functions return an array of TRUE or FALSE if the dates in the ranges are in December (true) or not (false). Then adding them together results in an array of 0's, 1's or 2's. For instance, if the fist date in E falls in December but the first one in H does not it returns 1 + 0 = 1 for that instance.
No need for the double "--" and what is SUMPRODYCT supposed to do? Don't you just want to SUM the resulting array?
And what 'random total' do you get for January?
Share your file or at least add a screenshot so that we can see what you are working on.
