Unique Dynamic Range

[jbeaucaire]

In the table shown below, the "AA" and "BB" and "CC" (etc) can really occur anywhere in the table. The full range of the table is actually B28:Y96.

I'm trying to count the number of "w" entries that appear in the randomly places "AA" section, in the example it resolves to B29:D32.

I'd like a dynamic range formula I could use to:

1) Spot the location of "AA"
2) Create a search range of the 12 cells offset below them as shown
3) Count the "w" entries in that dynamically located region

I'm stumped.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
27
28		6	AA		3	CC		0	WW	m	0	YY	m
29	Bowler 1	1	2	.	1	.	.
30		3b	w	.	.	.	2
31	wides
32	no balls
33		1	6	3	0	6	3	0	0	0	0	0	0
34
35		2	BB		5	DD		0	XX	m	0	ZZ	m
36	Bowler 2	.	.	.	1	2	.
37		.	.	.	.	w	2lb
38	wides	1
39	no balls	1
40		0	8	2	1	6	3	0	0	0	0	0	0
41
42		7	EE		8	GG		0		m	0		m
43	Bowler 3	1	.	.	6	.	.
44		2	4	.	1lb	1b	w
45	wides
46	no balls
47		0	6	7	1	6	6	0	0	0	0	0	0
48
Score Book

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True

The Results will go into this table:

Excel Workbook
	AO	AP	AQ	AR	AS
28	Over		Runs	Wkts	Total
29	1	AA	6	1	6
30	2	BB	2	0	8
31	3	CC	3	0	11
32	4	DD	5	1	16
33	5	EE	7	0	23
34	6	FF	0	0	23
35	7	GG	8	1	31
Score Book

 

[jbeaucaire]

Additional note... I could make short work of this with VBA, but it's a formula based solution (dynamic named range would be ideal) that I'm searching for. The project is VBA free so far.

 

[T. Valko]

Additional note... I could make short work of this with VBA, but it's a formula based solution (dynamic named range would be ideal) that I'm searching for. The project is VBA free so far.

If I understand the requirements...

=OFFSET(B28,MAX(IF(B28:Y96="AA",ROW(B28:Y96)))-ROW(B28)+1,MAX(IF(B28:Y96="AA",COLUMN(B28:Y96)))-COLUMN(B28)+1-2,4,3)

Creating the named range:

Name: MyRange
Refers to:

=OFFSET($B$28,MAX(IF($B$28:$Y$96="AA",ROW($B$28:$Y$96)))-ROW($B$28)+1,MAX(IF($B$28:$Y$96="AA",COLUMN($B$28:$Y$96)))-COLUMN($B$28)+1-2,4,3)

Book1
	A	B	C	D	E
30	_	_	AA	_	_
31	W	W	_	W	W
32	_	_	_	_	_
33	_	_	_	_	_
34	W	W	_	W	W
35	_	W	_	W	_
Sheet1

=COUNTIF(MyRange,"w")

=4

 

[mikerickson]

I also use a named approach.

Select a cell in row 29 and define

Name: MatchColumn RefersTo: =SUMPRODUCT(--(Sheet1!$A$1:$M$100=Sheet1!$AP29)*COLUMN(Sheet1!$A$1:$M$100))

Name: MatchRow RefersTo: =SUMPRODUCT(--(Sheet1!$A$1:$M$100=Sheet1!$AP29)*ROW(Sheet1!$A$1:$M$100))


Then put =OFFSET($A$1, matchRow-1, matchColumn-2) in AQ29
and =OFFSET($A$1, matchRow+4, matchColumn-2) in AR29
and =SUM(AQ$29:AQ29) in AS29
and drag down.

Handling the #REF error in the (non-existent) FF case can be done variously.

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>AP</b><td width=25><b>AQ</b><td width=25><b>AR</b><td width=25><b>AS</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>28</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Runs<td align="left" bgcolor=#FFFFFF>Wkts<td align="left" bgcolor=#FFFFFF>Total</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>29</b><td align="left" bgcolor=#FFFFFF>AA<td align="right" bgcolor=#FFFFFF>6<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>6</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>30</b><td align="left" bgcolor=#FFFFFF>BB<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>8</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>31</b><td align="left" bgcolor=#FFFFFF>CC<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>11</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>32</b><td align="left" bgcolor=#FFFFFF>DD<td align="right" bgcolor=#FFFFFF>5<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>16</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>33</b><td align="left" bgcolor=#FFFFFF>EE<td align="right" bgcolor=#FFFFFF>7<td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>23</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>34</b><td align="left" bgcolor=#FFFFFF>FF<td align="left" bgcolor=#FFFFFF>#REF!<td align="left" bgcolor=#FFFFFF>#REF!<td align="left" bgcolor=#FFFFFF>#REF!</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>35</b><td align="left" bgcolor=#FFFFFF>GG<td align="right" bgcolor=#FFFFFF>8<td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF>#REF!</tr>
</table>

 

[Domenic]

Here's a similar approach...

1) Select AR29

2) Define the following...

Name: Pos

Refers to:

=SMALL(IF($B$28:$M$47=$AP29,(ROW($B$28:$M$47)-ROW($B$28)+1)*10^5+(COLUMN($B$28:$M$47)-COLUMN($B$28)+1)),1)

Name: MyRange

Refers to:

=OFFSET(Sheet1!$B$28,LEFT(Pos,LEN(Pos)-5),RIGHT(Pos,5)-2,4,3)

3) Then try the following formula...

AR29, copied down:

=IFERROR(COUNTIF(MyRange,"w"),0)

or

=IF(COUNTIF($B$28:$M$47,$AP29)>0,COUNTIF(MyRange,"w"),0)

 

[jbeaucaire]

Thanks all, I just got back from church... will try these out and report back. Thank you all!

 

[jbeaucaire]

Well, I feel like a ditz. Turns out the owner of the sheet had already done the heavy-lifting on the sheet for counting the "w" entries in each section and placed in the cell OFFSET(5, -1) from where the "AA" was randomly found. I am sorry I didn't notice that earlier, because that allows a simple SUMIF() with offset ranges do the final work:

Excel Workbook
	AO	AP	AQ	AR	AS
28	Over		Runs	Wkts	Total
29	1	AA	6	1	6
30	2	BB	2	1	8
31	3	CC	3	1	11
32	4	DD	5	2	16
33	5	EE	7	2	23
34	6	FF	0	2	23
35	7	GG	8	3	31
36	8	HH	0	3	31
37	9	II	0	3	31
38	10	JJ	0	3	31
Score Book

Thanks for the ideas, though, this has actually been helpful, some interesting approaches to the Named Range feat. I will file away these examples for reference later. Thanks again for taking the time!

 

[T. Valko]

Well, I feel like a ditz. Turns out the owner of the sheet had already done the heavy-lifting on the sheet for counting the "w" entries in each section and placed in the cell OFFSET(5, -1) from where the "AA" was randomly found. I am sorry I didn't notice that earlier, because that allows a simple SUMIF() with offset ranges do the final work:

Excel Workbook
	AO	AP	AQ	AR	AS
28	Over		Runs	Wkts	Total
29	1	AA	6	1	6
30	2	BB	2	1	8
31	3	CC	3	1	11
32	4	DD	5	2	16
33	5	EE	7	2	23
34	6	FF	0	2	23
35	7	GG	8	3	31
36	8	HH	0	3	31
37	9	II	0	3	31
38	10	JJ	0	3	31
Score Book

Thanks for the ideas, though, this has actually been helpful, some interesting approaches to the Named Range feat. I will file away these examples for reference later. Thanks again for taking the time!

Good deal. Thanks for the feedback!