[SOLVED]Creation of a COUNTIF related macro

[Smurg]

Hi guys,

This is my first post to the forum, and was essentially my reason for joining up...as it is something that has got me quite stumped.

What I'm trying to do is create a macro based on a formula that can display the number instances of a certain word occurs in a range of data. I am also trying to whittle it down so that it can reveal how many times this word occured on any given day of a month.

My example here will be an =COUNTIF function searching for the word 'Logged'.

Now, I'm working with the month of May. Each day will have anything from 10 to 100+ entries. Say I have 1300 rows, within which is contained data for all 31 days of May. Another example: 06/05/2011 might have 43 entries. Within these rows, perhaps 25 of them relate to the word I am looking for (Logged), the others display various other pieces of data that also occured on this day.

It's very simply boiled down to; Have a day you want to search for, return all the instances of 'Logged' on that day; return/display this number in a cell.

The current formula that is simple enough to use, but is not specific enough to what I'm trying to find.I've got a separate worksheet setup for the results, the worksheet with the data on it, is low-and-behold called 'Data'. So the formula is as follows:

=COUNTIF(Data!F:F,"Logged")

This is fine for finding all of the instances across the whole range, but of course not specific to day.

I could do this by counting say 04/05/2011 (e.g F340:F413) and use the formula:

=COUNTIF(Data!F340:F413,"Logged")

Not only is this pretty much a manual process. But this is to become a template that will be easily able to apply to a new set of data on a month by month basis. Thus if I was singling out the data with specific cell ranges, it would not be cross-compatible with the next month -- where data will have varied greatly.

The end result of this, will end up with the user being able to enter a date, and have the number for the amount of 'Logged' that appear within the data for that day.

I've got a system whereby I can use hidden buttons assigned with macros over the top of a calendar design, so it'd be a one-click process.

Any help here would be much appreciated.

Thanks in advance!

[davegugg]

Is the date defined in a separate column? How will we know that rows 340 to 413 are 4/5/2011?

[Smurg]

Is the date defined in a separate column? How will we know that rows 340 to 413 are 4/5/2011?

Hi Dave,

Sorry, I realise I omitted that quite sizeable piece of information.

Yes, the date is in a separate column. The formula I posted is of course only checking the column that contains the word 'Logged' and the date is never even considered in the argument.

This is where I am having trouble, combining the date column to return the number within the 'Logged' column.

For examples sake: Column A is the Date column. Columns B to E are, for our purposes, considered extraneous data, whilst F is the only column where the word Logged will appear.

[davegugg]

If you were using Excel 2007, you could use the CountIfs formula. Since you are using 03, you will have to use a SumProduct:

Please Login or Register  to view this content.

This assumes the date you are trying to count Logged info for is in cell L1.

Or, if you need to find where the entire cell only says Logged, use this:

Please Login or Register  to view this content.

[Smurg]

If you were using Excel 2007, you could use the CountIfs formula. Since you are using 03, you will have to use a SumProduct:

Please Login or Register  to view this content.

This assumes the date you are trying to count Logged info for is in cell L1.

Or, if you need to find where the entire cell only says Logged, use this:

Please Login or Register  to view this content.

Thanks a lot for your help Dave,

I'm not quite sure what the issue is now; but it is returning a #NUM! error. I've attached you a screenshot of the spreadsheet and how it is set out. The L column is being used, but I changed the formula to read:

Please Login or Register  to view this content.

When I enter any date from 1/05/11, the cell that contains the above formula should display the correct number of 'Logged'? Wondering what the issue might be here; and if there is something I am missing.

Your help is very much appreciated.

[davegugg]

Well, your screenshot doesn't show cell N1, I'm assuming you have the date you are looking for typed into that cell?
It is much better to post a workbook than a screenshot.

It may be that in 03, you can't use the whole column. See if this works:

Please Login or Register  to view this content.

[Smurg]

Dave,

Thanks so much -- this appears to have been the issue, not being able to use the entire column (i.e. A:A).

It works a treat now! Thanks again for your help; much kudos to you sir.

Cheers.