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

[Gman2005]

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

[shg]

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

[shg]

Thanks, much better.

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

[Gman2005]

Thanks, much better.

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

Posted a workbook with sample data and results.

Thanks,
Gman2005

[shg]

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"}, "") ) )

[Gman2005]

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"}, "") ) )

Thanks for the fast reply, but I'm not sure if i'm doing this right. Why do I put it in C22?

Attached is the full file for your looking. Maybe that will help better.

Thanks,
Gman2005

[shg]

C22 in the workbook you posted previously.

[Gman2005]

C22 in the workbook you posted previously.

I did. But it doesn't do anything. All I get is zero. And I did Control Shift Enter.

[shg]

See attached.

[Gman2005]

OK, now I see.

I need to have the control numbers listed again.

Not solved yet. I'll be back for an additional formula.

Thanks for your help so far.


-Gman2005

[Gman2005]

Problem:

The formula does not remove the control numbers that only have 1 or 2 cycles. I can only have control numbers in my results with all three cycles (201, 202, 203).

[shg]

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?

[Gman2005]

ok,

you're right. it does. it puts their values to zero.

Now, how can I delete control numbers (in my results) that have values of zero?

Thanks so much for helping me.

-Gman2005

[shg]

Sorry, I don't grok the question.

[Gman2005]

Shg,

I need my formula edited.

See attached workbook.


Thanks much,

Gman2005

[shg]

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.

[Gman2005]

i wasn't asking for a formula to delete this time, but

Please Login or Register  to view this content.

will work for me to show those extra columns in my results

[shg]

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