Help with Date/array formula

P

plokhorst

Have 2 columns that could contain dates. Column A and B.
Need to count the number of each month in both columns. If
column B is empty, use month in column A. If B not empty use B.
This formula works in every month except JANUARY.
(Array) =SUM(IF(MONTH(IF(B1:B5="",A1:A5,B1:B5))=1,1,0))
Appears to read blank cells in range as 1(true). Have been fightin
this for way toolong and will appreciate any help
 
P

Peo Sjoblom

One way

=SUMPRODUCT(((B1:B5<>"")*(MONTH(B1:B5)=1)+(B1:B5="")*(MONTH(A1:A5)=1)*(A1:A5
<>"")))

entered normally
 
P

plokhorst

If anyone is interested, I finally figured it out - seems so simple

ARRAY Formula
=SUM(IF(A5:A11="",0,(IF(MONTH(B5:B11="",A5:A11,B5:B11))=1,1,0)))
 
Top