SumProduct is Counting Blanks as Having a "Month of January"

[bmxchick]

I am counting how many times the each month shows up, Every month so far is adding up right except January (because it is counting blanks) Can you help?


=SUMPRODUCT(--(MONTH('701'!$B$4:$B$127)=1))+SUMPRODUCT(--(MONTH('701'!$F$4:$F$127)=1))+SUMPRODUCT(--(MONTH('701'!$I$4:$I$127)=1))

 

[Greg Truby]

Re: Help...I have a deadline...

(a) please don't entitle your threads "help... I have a deadline" that does nothing to help board members figure out whether or not they can help with your problem when they see the title. Nor can others determine whether your thread is relevant to them should they hit it on a search. I have edited the title a bit.
.
(b) A zero (or blank) has a "month of 1", but a "day() of 0", so if you add that to your test, you should be fine.

=SUMPRODUCT(--((MONTH('701'!$B$4:$B$127)=1)*(DAY('701'!$B$4:$B$127)<>0)))+SUMPRODUCT(--((MONTH('701'!$F$4:$F$127)=1)*(DAY('701'!$F$4:$F$127)<>0)))+SUMPRODUCT(--((MONTH('701'!$I$4:$I$127)=1)*(DAY('701'!$I$4:$I$127)<>0)))

 

[BMRC]

Re: Help...I have a deadline...

Another option would be to subtract the blanks.

(Your formula) - (CountBlank('701'!$B$4:$B$127))

 

[bmxchick]

Thanks!! Sorry about the title, I didn't know what to put. I will give that a shot and let you know. I really appreciate it!!

 

[bmxchick]

AWESOME!!! Thanks so much!!! That worked.

=SUMPRODUCT(--((MONTH('701'!$B$4:$B$127)=1)*(DAY('701'!$B$4:$B$127)<>0)))+SUMPRODUCT(--((MONTH('701'!$F$4:$F$127)=1)*(DAY('701'!$F$4:$F$127)<>0)))+SUMPRODUCT(--((MONTH('701'!$I$4:$I$127)=1)*(DAY('701'!$I$4:$I$127)<>0)))

 

[Greg Truby]

Re: Help...I have a deadline...

(Your formula) - (CountBlank('701'!$B$4:$B$127))

Actually, would need all three, but yes, this too would work (and probably simpler to boot).

=SUMPRODUCT(--(MONTH('701'!$B$4:$B$127)=1))+SUMPRODUCT(--(MONTH('701'!$F$4:$F$127)=1))+SUMPRODUCT(--(MONTH('701'!$I$4:$I$127)=1))-COUNTBLANK('701'!$B$4:$B$127)-COUNTBLANK('701'!$F$4:$F$127)-COUNTBLANK('701'!$I$4:$I$127)

 

[BMRC]

A lot of people call me simple... Cheers.

 

[bmxchick]

Thanks so much Simple!!