Help with Date/array formula

  • Thread starter Thread starter plokhorst
  • Start date Start date
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
 
One way

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

entered normally
 
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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-demensional frequency distribution 4
Reference Formula Help 1
Complex SUMIF/COUNT IF 5
Formula Content linked to other cells formula Content 5
Help with VLOOKUP? 1
Sum using more than 6 nested IF functions 3
Excel Help with dates 2
Automatically Enter Formula in a Cell After Entering Data in Another Cell 0

Back
Top