Excel conditional functions

J

Joely_0523

New Member
Joined
Jan 24, 2013
Messages
8
Hi all -

I need to use "countif" functions for rows with certain values that work in multiples of 5. It's a bit complicated but I need to figure out a macro or short script I can run that counts occurences just for a select number of rows.

i.e.
A B C
[TABLE="width: 184"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]470[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]460P[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]So I need to run count if statements for every multiple of 5. I can not simply use the same number of rows, as it changes. I am trying to figure out how many 410 codes I have for every 5, and then do averages for those buckets of 5.


Any help is greatly appreciated!
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to MrExcel!

If I understand correctly, if your data (where the 410s are) resides in A2:A34, try:

Code: 
=IF(MOD((ROWS(A$2:A2)-1),5)+1=5,SUM(IF(LEFT(A$2:A2,3)="410",1))-SUM(C$1:C1),"")

In C2, committed with CTRL+SHIFT+ENTER and copied down.

Is this what you were after?

Matty
 
Upvote 0
I don't think that's exactly what I'm looking for. If we look at column C, I only want the rows that go up to value 5 (because I'm doing this in multiples of 5). In this case, 1-5 in column C ends at row 22. So for rows 1-22, I want to do a countif function for the 410s. So everytime I get a 410 in occurences 1-5, I want a frequency count. Then I need this to start again for rows 22 to 33 (where 6-10 happen, as this is my next multiple of 5). Does that make sense?
 
Upvote 0
I don't think that's exactly what I'm looking for. If we look at column C, I only want the rows that go up to value 5 (because I'm doing this in multiples of 5). In this case, 1-5 in column C ends at row 22. So for rows 1-22, I want to do a countif function for the 410s. So everytime I get a 410 in occurences 1-5, I want a frequency count. Then I need this to start again for rows 22 to 33 (where 6-10 happen, as this is my next multiple of 5). Does that make sense?
Click to expand...

Sorry, but I'm not sure I follow.

What do you mean by a "frequency count"?

It might be best if you show us exactly what you want the formula to deliver in a separate column. Could you post an illustrative example?

Matty
 
Upvote 0
Sure, so in the example 410s refer to our code for throws. 410R = strong throw, 410Y = medium throw, 410B = weak throw, 410U = unsuccessful throw. So I have a countif statement to return a 1 if a "410" is found (column B). Then I summed those to count how many times I see the "410" code (column C). Now, I am making averages for ever 5 throws of strong, medium, weak, unsuccessful. I need to figure out how to count the number of 410R, 410Y, etc. for every 5 times I see a 410 code (hence the multiple of 5 part).

Hope this makes sense but it's a bit tricky!

[TABLE="width: 652"]
<colgroup><col><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Time[/TD]
[TD]Throw Attempts[/TD]
[TD]Throw Unsuccess
[/TD]
[TD]Throw Success[/TD]
[TD]Strong[/TD]
[TD]Medium[/TD]
[TD]Weak[/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD]Time[/TD]
[TD]Throw Attempts[/TD]
[TD]Throw Unsuccess[/TD]
[TD]Throw Success[/TD]
[TD]Strong[/TD]
[TD]Medium[/TD]
[TD]Weak[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]460P[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]430[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]470[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]480[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]420[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD]Time[/TD]
[TD]Throw Attempts[/TD]
[TD]Throw Unsuccess[/TD]
[TD]Throw Success[/TD]
[TD]Strong[/TD]
[TD]Medium[/TD]
[TD]Weak[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410U[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]410R[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
The layout is slightly different, but is the following acceptable?

Layout as follows...

*ABCDEFGH
Throw TypeTimeThrow AttemptsThrow UnsuccessfulThrow SuccessStrongMediumWeak
*******
410U*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
410R*******
*******
*******
*******
*******
*******
410R*******
*******
410B*******
*******
*******
410B
*******
410B*******
*******
410R*******
460P*******
*******
*******
*******
*******
*******
*******
*******
410U*******
*******
410R*******
*******
410B
*******
410R*******
*******
*******
410U*******
*******
410U*******
*******
410U*******
*******
410R
*******

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: right"]430[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: right"]470[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: right"]480[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]52[/TD]

[TD="bgcolor: #cacaca, align: center"]53[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]54[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]55[/TD]
[TD="align: right"]400[/TD]

</tbody>

Formulas as follows...

B2, copied down:

Code: 
=IF(C2<>"",COUNT(C$2:C2),"")

C2, committed with CTRL+SHIFT+ENTER and copied down:

Code: 
=IF(AND(LEFT(A1,3)<>"410",LEFT(A2,3)="410",MOD((SUM(IF(LEFT(A$2:A2,3)="410",1))-1),5)+1=5),SUM(IF(LEFT(A$2:A2,3)="410",1)),"")

D2, committed with CTRL+SHIFT+ENTER and copied down:

Code: 
=IF(C2<>"",SUM(IF(A$2:A2="410U",1))-SUM(D$1:D1),"")

E2, copied down:

Code: 
=IF(B2<>"",C2-D2,"")

F2, copied down:

Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410R")-SUM(F$1:F1),"")

G2, copied down:

Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410Y")-SUM(G$1:G1),"")

H2, copied down:
Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410B")-SUM(H$1:H1),"")

Matty
 
Upvote 0
Matty said:
The layout is slightly different, but is the following acceptable?

Layout as follows...

*ABCDEFGH
Throw TypeTimeThrow AttemptsThrow UnsuccessfulThrow SuccessStrongMediumWeak
*******
410U*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
410R*******
*******
*******
*******
*******
*******
410R*******
*******
410B*******
*******
*******
410B
*******
410B*******
*******
410R*******
460P*******
*******
*******
*******
*******
*******
*******
*******
410U*******
*******
410R*******
*******
410B
*******
410R*******
*******
*******
410U*******
*******
410U*******
*******
410U*******
*******
410R
*******

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: right"]430[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: right"]470[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: right"]480[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]52[/TD]

[TD="bgcolor: #cacaca, align: center"]53[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]54[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]55[/TD]
[TD="align: right"]400[/TD]

</tbody>

Formulas as follows...

B2, copied down:

Code: 
=IF(C2<>"",COUNT(C$2:C2),"")

C2, committed with CTRL+SHIFT+ENTER and copied down:

Code: 
=IF(AND(LEFT(A1,3)<>"410",LEFT(A2,3)="410",MOD((SUM(IF(LEFT(A$2:A2,3)="410",1))-1),5)+1=5),SUM(IF(LEFT(A$2:A2,3)="410",1)),"")

D2, committed with CTRL+SHIFT+ENTER and copied down:

Code: 
=IF(C2<>"",SUM(IF(A$2:A2="410U",1))-SUM(D$1:D1),"")

E2, copied down:

Code: 
=IF(B2<>"",C2-D2,"")

F2, copied down:

Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410R")-SUM(F$1:F1),"")

G2, copied down:

Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410Y")-SUM(G$1:G1),"")

H2, copied down:
Code: 
=IF(C2<>"",COUNTIF(A$2:A2,"410B")-SUM(H$1:H1),"")

Matty
Click to expand...



Ah sorry for the delay Matty! That seemed to work out pretty well though. I really appreciate your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Average Function
Replies
8
Views
365
Peter_SSs
Peter_SSs
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
307
GeneBF
G
T
  • Solved
Categorizing and adding data with multiple conditions
Replies
5
Views
514
kevin9999
K
J
  • Solved
Data Validation with Multiple Conditions
Replies
4
Views
612
dreid1011
dreid1011

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top