Excel conditional functions

[Joely_0523]

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

470 0 0 410U 1 1 400 0 1 400 0 1 400 0 1 400 0 1 400 0 1 400 0 1 400 0 1 400 0 1 003 0 1 001 0 1 400 0 1 410R 1 2 400 0 2 400 0 2 410R 1 3 400 0 3 410B 1 4 400 0 4 410B 1 5 400 0 5 410B 1 6 400 0 6 410R 1 7 460P 0 7 400 0 7 410U 1 8 400 0 8 410R 1 9 400 0 9 410B 1 10 400 0 10 <colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody> </tbody>
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!

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[Matty]

Welcome to MrExcel!

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

=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

 

[Joely_0523]

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?

 

[Matty]

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?

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

 

[Joely_0523]

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!

400	0	0	Time	Throw Attempts	Throw Unsuccess	Throw Success	Strong	Medium	Weak
410U	1	1	1	5	1	4	2	0	2
400	0	1
400	0	1
400	0	1
400	0	1
400	0	1
400	0	1
400	0	1
400	0	1
003	0	1
001	0	1
400	0	1
410R	1	2
400	0	2
400	0	2
400	0	2
400	0	2
400	0	2
410R	1	3
400	0	3
410B	1	4
400	0	4
400	0	4
410B	1	5
400	0	5
410B	1	6	Time	Throw Attempts	Throw Unsuccess	Throw Success	Strong	Medium	Weak
400	0	6	2	10	4	6	4	0	2
410R	1	7
460P	0	7
430	0	7
003	0	7
470	0	7
001	0	7
480	0	7
420	0	7
400	0	7
410U	1	8
400	0	8
410R	1	9
400	0	9
410B	1	10
400	0	10
410R	1	11	Time	Throw Attempts	Throw Unsuccess	Throw Success	Strong	Medium	Weak
400	0	11	3	10	3	7	4	2	1
400	0	11
410U	1	12
400	0	12
410U	1	13
400	0	13
410U	1	14
400	0	14
410R	1	15
400	0	15

<colgroup><col><col span="5"><col><col span="3"></colgroup><tbody>
</tbody>

 

[Matty]

The layout is slightly different, but is the following acceptable?

Layout as follows...

*	A	B	C	D	E	F	G	H
1	Throw Type	Time	Throw Attempts	Throw Unsuccessful	Throw Success	Strong	Medium	Weak
2	400	*	*	*	*	*	*	*
3	410U	*	*	*	*	*	*	*
4	400	*	*	*	*	*	*	*
5	400	*	*	*	*	*	*	*
6	400	*	*	*	*	*	*	*
7	400	*	*	*	*	*	*	*
8	400	*	*	*	*	*	*	*
9	400	*	*	*	*	*	*	*
10	400	*	*	*	*	*	*	*
11	400	*	*	*	*	*	*	*
12	3	*	*	*	*	*	*	*
13	1	*	*	*	*	*	*	*
14	400	*	*	*	*	*	*	*
15	410R	*	*	*	*	*	*	*
16	400	*	*	*	*	*	*	*
17	400	*	*	*	*	*	*	*
18	400	*	*	*	*	*	*	*
19	400	*	*	*	*	*	*	*
20	400	*	*	*	*	*	*	*
21	410R	*	*	*	*	*	*	*
22	400	*	*	*	*	*	*	*
23	410B	*	*	*	*	*	*	*
24	400	*	*	*	*	*	*	*
25	400	*	*	*	*	*	*	*
26	410B	1	5	1	4	2	0	2
27	400	*	*	*	*	*	*	*
28	410B	*	*	*	*	*	*	*
29	400	*	*	*	*	*	*	*
30	410R	*	*	*	*	*	*	*
31	460P	*	*	*	*	*	*	*
32	430	*	*	*	*	*	*	*
33	3	*	*	*	*	*	*	*
34	470	*	*	*	*	*	*	*
35	1	*	*	*	*	*	*	*
36	480	*	*	*	*	*	*	*
37	420	*	*	*	*	*	*	*
38	400	*	*	*	*	*	*	*
39	410U	*	*	*	*	*	*	*
40	400	*	*	*	*	*	*	*
41	410R	*	*	*	*	*	*	*
42	400	*	*	*	*	*	*	*
43	410B	2	10	1	9	2	0	2
44	400	*	*	*	*	*	*	*
45	410R	*	*	*	*	*	*	*
46	400	*	*	*	*	*	*	*
47	400	*	*	*	*	*	*	*
48	410U	*	*	*	*	*	*	*
49	400	*	*	*	*	*	*	*
50	410U	*	*	*	*	*	*	*
51	400	*	*	*	*	*	*	*
52	410U	*	*	*	*	*	*	*
53	400	*	*	*	*	*	*	*
54	410R	3	15	3	12	2	0	0
55	400	*	*	*	*	*	*	*

<tbody>
</tbody>

Formulas as follows...

B2, copied down:

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

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

=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:

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

E2, copied down:

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

F2, copied down:

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

G2, copied down:

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

H2, copied down:

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

Matty

 

[Joely_0523]

The layout is slightly different, but is the following acceptable?

Layout as follows...

*	A	B	C	D	E	F	G	H
1	Throw Type	Time	Throw Attempts	Throw Unsuccessful	Throw Success	Strong	Medium	Weak
2	400	*	*	*	*	*	*	*
3	410U	*	*	*	*	*	*	*
4	400	*	*	*	*	*	*	*
5	400	*	*	*	*	*	*	*
6	400	*	*	*	*	*	*	*
7	400	*	*	*	*	*	*	*
8	400	*	*	*	*	*	*	*
9	400	*	*	*	*	*	*	*
10	400	*	*	*	*	*	*	*
11	400	*	*	*	*	*	*	*
12	3	*	*	*	*	*	*	*
13	1	*	*	*	*	*	*	*
14	400	*	*	*	*	*	*	*
15	410R	*	*	*	*	*	*	*
16	400	*	*	*	*	*	*	*
17	400	*	*	*	*	*	*	*
18	400	*	*	*	*	*	*	*
19	400	*	*	*	*	*	*	*
20	400	*	*	*	*	*	*	*
21	410R	*	*	*	*	*	*	*
22	400	*	*	*	*	*	*	*
23	410B	*	*	*	*	*	*	*
24	400	*	*	*	*	*	*	*
25	400	*	*	*	*	*	*	*
26	410B	1	5	1	4	2	0	2
27	400	*	*	*	*	*	*	*
28	410B	*	*	*	*	*	*	*
29	400	*	*	*	*	*	*	*
30	410R	*	*	*	*	*	*	*
31	460P	*	*	*	*	*	*	*
32	430	*	*	*	*	*	*	*
33	3	*	*	*	*	*	*	*
34	470	*	*	*	*	*	*	*
35	1	*	*	*	*	*	*	*
36	480	*	*	*	*	*	*	*
37	420	*	*	*	*	*	*	*
38	400	*	*	*	*	*	*	*
39	410U	*	*	*	*	*	*	*
40	400	*	*	*	*	*	*	*
41	410R	*	*	*	*	*	*	*
42	400	*	*	*	*	*	*	*
43	410B	2	10	1	9	2	0	2
44	400	*	*	*	*	*	*	*
45	410R	*	*	*	*	*	*	*
46	400	*	*	*	*	*	*	*
47	400	*	*	*	*	*	*	*
48	410U	*	*	*	*	*	*	*
49	400	*	*	*	*	*	*	*
50	410U	*	*	*	*	*	*	*
51	400	*	*	*	*	*	*	*
52	410U	*	*	*	*	*	*	*
53	400	*	*	*	*	*	*	*
54	410R	3	15	3	12	2	0	0
55	400	*	*	*	*	*	*	*

<tbody>
</tbody>

Formulas as follows...

B2, copied down:

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

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

=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:

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

E2, copied down:

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

F2, copied down:

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

G2, copied down:

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

H2, copied down:

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

Matty

Ah sorry for the delay Matty! That seemed to work out pretty well though. I really appreciate your help!

 

[Matty]

Ah sorry for the delay Matty! That seemed to work out pretty well though. I really appreciate your help!

No problem. Glad I could help!

Matty