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:

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

Thanks,

Dan

**mrice** · 05-08-2007, 02:03 PM

Here's another one - a bit more stepwise but gives the same answer and is adaptable to periods other than complete calendar months.

**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?

Please Login or Register  to view this content.

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?

**HurricaneDan** · 05-09-2007, 12:57 PM

Sorry for not being more specific. Not only does the end user want month and name, they are now wanting a break down by hour. The problem comes in with the fact that time is not required. So there are some entries that will not have a time entry. If no time is entered it actually enters the string "Left Blank" in another cell on the same row.

Since I am storing the time as a string I can also use the string "blank" in the field where time is normally stored, this will atleast acknowledge something is in the field. The problem I have with this method is that when finding the hour (to group these items) I am looking for the ":" - this does not exist when a field is either left empty or has the string "blank" in it.

Does this help or just clutter the matter?

Dan

**NBVC** · 05-09-2007, 01:15 PM

So you want another criteria added to the Sumproduct() formula?

Since Excel stores times as numbers...you can use the Isnumber() function like so....

=SUMPRODUCT(--(MONTH(E2:E50)=5),--(A2:A50="Joe Doe"),--ISNUMBER(C2:C50)) where C2:C50 is the range with times in it. This will not count items with a text string or blank in it.

If this still isn't what you're looking for, then please post a zipped sample sheet....a picture is worth a thousand words.

**HurricaneDan** · 05-09-2007, 01:33 PM

Okay, I have attached the zipped file...please don't laugh too much at my feeble attempts.

The first thing you will notice is the user form, just close out using the window close button.

Then on the sheet named 'JET Data Tracking' look at column/row AC3 - this is where some counted data should exist. As you can see from my formula it does the following: Is the time with in the range (in this case 2), is it AM or PM (PM), correct month (5), year (2007).

Any help with organizing these times would be appreciated.

I am also up to a complete rework of this to streamline the process. As you can see the data has to be broken down by year, month, time, person etc.

Dan

**NBVC** · 05-09-2007, 02:34 PM

I'm hoping this does it.... I had to take some guesses as to what exactly you were referencing....

but basically, within my formula, I converted each time entry into a number and used the Int() function to get the integer value (i.e. the round O'clock number).. then I compared it against the Int() time values located in your header row of the "Jet Data Tracking" sheet.

To avoid the #Value! errors, I converted to Count(IF()) formula which must be confirmed with CTRL+SHIFT+ENTER to work.... then you can copy the formula down and right or left.

This is the formula in AC2: =COUNT(IF((INT(JETRecordTracking.xls!Time_Of_Call*24)=INT(MID(AC$1,FIND(CHAR(10),AC$1)+1,LEN(AC$1))*24))*(MONTH(JETRecordTracking.xls!Date_of_Call)='JET Data Tracking'!$A2)*(YEAR(JETRecordTracking.xls!Date_of_Call)='JET Data Tracking'!$B2),1))

I hope this works out to be what you needed...

**HurricaneDan** · 05-09-2007, 03:09 PM

So I tried out the Count(IF( )) portion but now it returns the same data for all fields. See the reworked file in the attached zip file. Maybe it is something I should be changing.

As for what things are referencing: The time that is being broke down is based on 'jet response report' column C, the date is from column E. I hope this helps some. I used the dynamic range technique from above to get the names for the fields.

Does that help?

Also, is there an easier way to count rows by month, by hour? That is really what it comes down to.

Thanks,
Dan

**NBVC** · 05-09-2007, 03:20 PM

I don't see anything different in the sheet you attached from the sheet I sent.

if you copy my formula down and across, you should see 1's and 0's based on your entries in the Response Report sheet.

Am I misunderstanding your needs?

**HurricaneDan** · 05-09-2007, 04:11 PM

No you are not misunderstanding. It is something with me.

When I use your copy of the workbook and drag cells it works correctly. If I copy and paste the formula it gives different results.

What I notice is that the formula when I click and drag is that in the formula bar the formula is wrapped in {}, when I copy and paste it is not. What causes this?

Sorry for not further testing before responding.

You have been a great help.

Dan

**NBVC** · 05-09-2007, 04:15 PM

Originally Posted by HurricaneDan

No you are not misunderstanding. It is something with me.

When I use your copy of the workbook and drag cells it works correctly. If I copy and paste the formula it gives different results.

What I notice is that the formula when I click and drag is that in the formula bar the formula is wrapped in {}, when I copy and paste it is not. What causes this?

Sorry for not further testing before responding.

You have been a great help.

Dan

Those {} brackets means the formula is considered an array formula, it requires that you confirm the formula by holding the Ctrl and Shift keys and pressing the Enter key. The {} are entered autimatically by Excel. So if you copy and paste my formula (or make any adjustments to the formula) you must reconfirm it with the CSE key combo...then you can copy it around.

**HurricaneDan** · 05-09-2007, 04:27 PM

After I posted my response I did a little looking for Array Formulas and found the answer to my own question: http://www.mrexcel.com/tip011.shtml is a pretty good link to explain what it does if not when to use it.

Thanks again for the help and the patience.

**NBVC** · 05-09-2007, 04:30 PM

Originally Posted by HurricaneDan

After I posted my response I did a little looking for Array Formulas and found the answer to my own question: http://www.mrexcel.com/tip011.shtml is a pretty good link to explain what it does if not when to use it.

Thanks again for the help and the patience.

Glad you found a good explanation.... there's so much to learn in Excel...

You're welcome. Good Luck.