count number of records per month

**HurricaneDan** · 05-08-2007, 01:40 PM

I have a spreadsheet that users are filling in using a userform. Due to the fact that some data may be pre/post dated for entry I am trying to find a way that I can count the number of records per month.

What I have been trying to use (with no luck) is COUNTIF:

=COUNTIF(E2:E50,TEXT(MONTH(E2:E50), "00"), "05")

What this should do is count the number of times an entry has a month of "05" by testing the month of each entry in a range in column E from 2 to 50.

I would also like this to compute using any filled in rows, not just a static number. It is not a problem to move this to vba if that is easier, I just need some help pointing me in the right direction.

Thanks,

Dan

**VBA Noob** · 05-08-2007, 01:51 PM

Hi,

Try

=SUMPRODUCT(--(MONTH(E2:E50)=5))

Use a dynamic range instead of E2:E50. Link will show you how

http://www.contextures.com/xlNames01.html#Dynamic

VBA Noob

**NBVC** · 05-08-2007, 01:52 PM

Try:

=SUMPRODUCT(--(TEXT(MONTH(E2:E50),"00")="05"))

If your date is entered as a true date and not a text string, then this too should work

=SUMPRODUCT(--(MONTH(E2:E50)=5))

**HurricaneDan** · 05-08-2007, 01:59 PM

Thanks to both of you!

Now if you do not mind answering a follow up, what does the '--' do?

And if I wanted to know how many times the same person reported something for the month would it be like this?

=SUMPRODUCT(--(MONTH(E2:E50)=5)*(A2:A50="Joe Doe"))

Thanks,

Dan

**NBVC** · 05-08-2007, 02:11 PM

Originally Posted by HurricaneDan

Thanks to both of you!

Now if you do not mind answering a follow up, what does the '--' do?

And if I wanted to know how many times the same person reported something for the month would it be like this?

=SUMPRODUCT(--(MONTH(E2:E50)=5)*(A2:A50="Joe Doe"))

Thanks,

Dan

Close.... =SUMPRODUCT(--(MONTH(E2:E50)=5),--(A2:A50="Joe Doe"))

The "--" is a double unary and acts as a coercer to change Trues and Falses to 1's and 0's, respectively, so that the Sumproduct can perform the necessary arithmetic to get the result.

**HurricaneDan** · 05-09-2007, 10:25 AM

NBVC,

Thanks again for the help. I have run into one issue where it can be an either or answer for a question. What do you do if a field is blank midway through a column? It causes the #VALUE! error.

Thanks again,

Dan

**NBVC** · 05-09-2007, 10:29 AM

Originally Posted by HurricaneDan

NBVC,

Thanks again for the help. I have run into one issue where it can be an either or answer for a question. What do you do if a field is blank midway through a column? It causes the #VALUE! error.

Thanks again,

Dan

Can you be more specific? Which column? And why blank?