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 ...
I'm sorry, but formula doesn't ignore texts. ISNUMBER(text) returns zero, but MONTH(text) returns #VALUE!. If multiply zero on (#VALUE!=12) result will be #VALUE!, and SUMPRODUCT with at least one element with error also returns an error.
Yes, you are right;🙂 the formula does not ignore texts in its current form. I think with this formula you can ignore both spaces and zeros....or am I doing something wrong here too?
=SUMPRODUCT(--(IF(ISNUMBER('Andy''s Team'!E2:E32)*('Andy''s Team'!E2:E32<>0), MONTH('Andy''s Team'!E2:E32), 0)=12)
+ --(IF(ISNUMBER('Andy''s Team'!H2:H32)*('Andy''s Team'!H2:H32<>0), MONTH('Andy''s Team'!H2:H32), 0)=12))
I hope I'm not wrong with "--" again, if I am, I'd be happy about a correction🙂.