Counting empty cells between two cells with data in them.

[Dartmouth_jock]

Hi folks, I have a sheet where column c contains an employee's name and data is in columns d-j. There are a variable number of rows between employees where there is data in rows d-j but not in row c. Is there a way to count the number of rows between the employees? I am trying to build a formula to extract data for each employee based on customer interactions. Only some of the rows meet the criteria I am looking for.

employee 1 total amount
case 1 case1.1
case 2 case 2,2
employee 2 total amount
case 1 case1.1
case 2 case 2,2
case 3 case3.1
case 4 case 4,2
employee 3 total amount
case 1 case1.1
case 2 case 2,2

Case 1 for employee one meets criteria and case 3 for employee 2 and case 2 for employee three. Just wondering how to count the number of rows between employees using either a formula or vba.

[Pete_UK]

You can see that formatting on these posts is terrible, so it is not clear how your data is laid out - please attach a sample Excel workbook instead (the FAQ describes how to).

Pete

[Dartmouth_jock]

Here is the sample, I removed the identifiable data name 1 and name 2 are the employees and the records in between contain the data I am working with.

[Pete_UK]

So, with reference to this file, can you explain more clearly what you want to achieve. You seem to have changed a few things compared to your first post (name is in column A, for example, not C, no data in columns I and J).

Pete

[Dartmouth_jock]

Sorry I copied from a business spread sheet to protect the information. I want to count the number of rows dynamically between employee names so I can create a formula that allows me to extract data about the employees interactions. The number of transactions vary between employees. The spreadsheet is a report on fee transactions. The criteria used to create the spreadsheet is not accurate and I have to produce a report to show whether it is an employee error or a report error and then take appropriate action - touch base with the employee to enhance training or report to the boss the accurate rate of collection.

I want to count blank cells and reset the count to zero when I hit a cell with an employee's name in it. I have written this =IF(C6=ISTEXT(C6),ROWS(C6:C6),0) which gives me a score of one for each row of data for an employee. I want to write something that stops when it hits the the next zero and gives me a row count so I can return the multiple data I am looking for based on critera maybe through vlookup. I can't fix the start row as $c$6 as the next employee is at c31 and need to restart there with the next employee at c38 This is in the real spread sheet, in the example it would be a1,a25, and a32.

As you can tell I am knowledgeable enough to be dangerous but not solve the problem.

[Pete_UK]

I'm still not sure what you want to achieve. Referring to your example file, might it be that in cell I3 (same row as name1 appears) you want the number 25, which is the number of rows of data associated with that name, and that in cell I28 you would want 7, which is the number of rows for that name (before the end of your table)?

Should I insert two new columns A and B into the file you submitted to make it of the same layout of your real data, or will you submit a more representative example file?

Pete

[Dartmouth_jock]

Hi Pete, thanks for taking the time and having patience with me. What I am trying to do is write something that will count the number of entries for an agent and allow me to extract specific data. The search has to stop at the next agent and start again. I have created a formula that counts the number of entries per agent and resets the count to zero for the next agent but I cant figure out how to use that information to start the search at cell containing zero for the next agent. This is the layout for the spreadsheet I receive.

Thanks again

[Pete_UK]

Would it not be easier to fill column C (in your latest file) with the name of each agent?

This can be done quite easily (and quickly) by selecting all the cells in the table for that column (i.e. from C2 to C13 in the example file), and then pressing F5 (GoTo), then clicking on Special... and clicking against Blanks and clicking OK. (So now only the blanks in that column will be selected, starting with cell C3). Then you type = and click on the cell immediately above the active cell, and then CTRL-Enter together, and that will replicate that formula into all the blank cells, giving you the name of each agent in every cell.

I'm not sure what you want to do beyond that, but you can easily use a SUMIF formula (or COUNTIF) to get a total (or count) of all the entries for each agent.

Hope this helps.

Pete