Adding rows with Control Number that only repeat 3 times: Formula

Hi,

I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.

Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.

So I need something that can take each control number that has three cycles and add their # of 0:15 together.

The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.

Ultimately I would like the results on a separate sheet.

This is only a part of the file, there are over 1000 different control numbers.

Please Login or Register  to view this content.

I hope I explained it well enough.

Thanks,
Gman2005

Please read the forum rules, and then amend your thread title.

Thanks, much better.

Post a workbook with a meaningful sample of data, and a few manually-worked answers?

In C22 and copy down this array formula:

=IF( AND( ISNUMBER( MATCH({201,202,203} & B22, $A$2:$A$17 & $B$2:$B$17, 0) ) ), SUMPRODUCT( OR($A$2:$A$17={201,202,203}) * ($B$2:$B$17=B22) * $C$2:$C$17), 0)

You MUST confirm this formula with Ctrl+Shift+Enter, not just Enter.

The formula in C2 and down can be simplified to this regular formula:

=SUM(LEN(D2) - LEN(SUBSTITUTE(D2, {"K","L","M","N","O","P","Q"}, "") ) )

C22 in the workbook you posted previously.

See attached.

The formula does not remove the control numbers that only have 1 or 2 cycles

I don't think so, Gman.

What result do you see in C22?

What result do you see if you delete row 2, 3, or 4?

Sorry, I don't grok the question.

Formulas can't delete anything. Why not just leave them? For one thing, it makes clear that they have not been omitted from the calculation.

You can do a two-way sort do they end up at the bottom of the list (as they coincidently happen to now).

I would change the formula in Q2 to =SUMPRODUCT( ($B$2:$B$11=$O2) * ($P2>0) * D$2:D$11), then copy across and down.

So it's sorted? And if so, would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes