Sumproduct in given month problem

[chriskay]

Hi!

I am working on an ongoing sheet in which I am writing dates into a column. I am trying to use the sumproduct function to count the number of dates that I have written that lies within a specific month.

Example:

(dd-mm-year)
A1: 01-01-2017
A2: 03-01-2017
A3: 21-01-2017
A4: 02-02-2017
A5: 26-02-2017
A6: 05-03-2017

Using the following function, I can count how many dates that is in January:

=SUMPRODUCT(1*(MONTH(A1:A20)=1))

Now, the formula should be returning the value of 3, but since it is an ongoing sheet, I need the matrix to include all rows in the column. But then I get the month count wrong, because a blank string apparently counts as "month=1".

For the above example with the given formula, it returns a value of 17.

How do I make the formula to only count the strings in which I wrote a date?

(sorry for bad english) :-)

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[chriskay]

Hmm that didn't work at all, sorry :-)

As mentioned, I need the function to include several rows, in which some of them are blank, so I can fill them out as the time goes by - so that I don't have to change the formula every time I add a new date.

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[chriskay]

I attached an example-sheet.

However, it is written in a danish Excel, so I hope you're able to read the formulas.

[shukla.ankur281190]

Ok try

Please Login or Register  to view this content.

[chriskay]

Thank you, worked perfectly!! :-)

[shukla.ankur281190]

Thanks for Feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.