Count adjacent column after given criteria

[mnnat]

Hello, I'm looking for formula help. What I'm looking to do is COUNT the number of instances a value shows up in Column B, AFTER its value shows up in Column A. Consider the following data set:

Col A Col B
Jim Bob
Bob harry
Harry Jane
Bill Jane
Jim Bob
Jane Jim
Bob Jane
Harry Jim
Bill Jane
Jane Jim

1) What I'm looking for is counting the number of "Jim" in Column B AFTER the most recent occurrence of Jim in Column A (in this case, the 5th position down in Column A). The answer here should be "3" if I'm using the COUNT function.

In some form I think I need the OFFSET, MATCH and COUNTIF, but I can't wrap my arms around how to write it.

In other terms, the formula could be said as "Find the most recent occurrence of Jim in Column A, and count how many occurrences of Jim are in Column B after that point."

Thank you for the assistance.

 

[Robert Mika]

Try this:

=COUNTIF(INDEX(B1:B100,MAX(IF(A1:A100="Jim",ROW(A1:A100)))):INDEX(B1:B100,MATCH("zzzzz",A1:A100)),"jim")
Array formula- confirm CTRL+SHIFT+ENTER

 

[Peter_SSs]

Find the most recent occurrence of Jim in Column A, ...

1. Will there always be one?

2. If not, what result would you have wanted if there was no 'Jim' in column A and column B was as in your sample above?


(Robert, assuming the data is in A1:B10, change the 'Jane' in A10 to 'Jim' and observe your formula's result. It *may* be what the OP wants but with the normal meaning of 'after' I think the result should be 0)

 

[Robert Mika]

Hi Peter,
Thank you for your coments and advice.
In this case:
=COUNTIF(INDEX(B1:B100,MAX(IF(A1:A100="Jim",ROW(A1:A100)))+1):INDEX(B1:B100,MATCH("zzzzz",A1:A100)+1),"Jim")
(array formula confirm CTRL+SHIFT+ENTER)

The COUNTIF function should start to count Jims in column B one row after (or below) last "Jim" in column A.
Now the "Jane"-"Jim" combination comes to 0.

 

[Aladin Akyurek]

Let A1:B11 on Sheet1 house the sample you posted...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-STYLE: italic; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 14.4pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl65 height=19 width=64>Field-1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-STYLE: italic; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl65 width=64>Field-2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl66 height=19>Jim</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Bob</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67>harry</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl68 height=19>Harry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Jane</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19>Bill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67>Jane</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl66 height=19>Jim</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Bob</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19>Jane</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67>Jim</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl68 height=19>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Jane</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19>Harry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67>Jim</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Verdana; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl68 height=19>Bill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Jane</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67 height=19>Jane</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67>Jim</TD></TR></TBODY></TABLE>

Define BigStr by means of Insert | Name | Define or Formulas | Name Manager
as referring to:

=REPT("z",255)

Define BigNum as referring to:

=9.99999999999999E+307

or just to:

=9.99E+307

Define Lrec as referring to:

=MATCH(BigStr,Sheet1!$B:$B)

Now we can invoke in order to "Find the most recent occurrence of Jim in Column A,
and count how many occurrences of Jim are in Column B after that point."

=COUNTIF(OFFSET(INDEX(B:B,Lrec),0,0,-(Lrec-MATCH(BigNum,IF(A2:INDEX(A:A,Lrec)="jim",1))-1)),"jim")

 

[Peter_SSs]

Aladin

I presume that formula also needs to be array-entered, but unless I haven't implemented it correctly (I have allowed for different sheet names in defining 'Lrec') I think it fails in some circumstances. Whether these circumstances are possible with the OP's data I don't know.

Excel Workbook
	A	B	C	D
1	Jim	Bob		#REF!
2	Bob	harry
3	Harry	Jane
4	Bill	Jane
5	Jim	Bob
6	Jane	Jim
7	Bob	Jane
8	Harry	Jim
9	Bill	Jane
10	Jim	Jim
11
Ex A

Excel Workbook
	A	B	C	D
1	Jim	Bob		2
2	Bob	harry
3	Harry	Jane
4	Jim	Bob
5	Jane	Jim
6	Bob	Jane
7	Harry	Jim
8	Bill	Jane
9	Tom	Bob
10	Jim
11	Ken
12
Ex B

This seems to work for combinations I have tried:

Excel Workbook
	A	B	C	D
1	Jim	Bob		Jim
2	Bob	harry		3
3	Harry	Jane
4	Bill	Jane
5	Jim	Bob
6	Jane	Jim
7	Bob	Jane
8	Harry	Jim
9	Bill	Jane
10	Jane	Jim
11
EX C

 

[Aladin Akyurek]

Aladin

I presume that formula also needs to be array-entered, but unless I haven't implemented it correctly (I have allowed for different sheet names in defining 'Lrec') I think it fails in some circumstances. Whether these circumstances are possible with the OP's data I don't...

Yes, the formula needs to be confirmed with control+shift+enter, not just
with enter. If the last 2-item record consists of Jim and Jim, we will indeed
get a #REF! error, which would be an informative result. This was not
unintended. We can augment the formula with control expressions (thereby
making it more expesive), if such result must be avoided. By the way, Lrec
is used in order to create a dynamic named range. In Lrec we need to refer to the sheet housing the data of interest, so I have no idea what you mean by
different sheet names.

 

[T. Valko]

Hello, I'm looking for formula help. What I'm looking to do is COUNT the number of instances a value shows up in Column B, AFTER its value shows up in Column A. Consider the following data set:

Col A Col B
Jim Bob
Bob harry
Harry Jane
Bill Jane
Jim Bob
Jane Jim
Bob Jane
Harry Jim
Bill Jane
Jane Jim

1) What I'm looking for is counting the number of "Jim" in Column B AFTER the most recent occurrence of Jim in Column A (in this case, the 5th position down in Column A). The answer here should be "3" if I'm using the COUNT function.

In some form I think I need the OFFSET, MATCH and COUNTIF, but I can't wrap my arms around how to write it.

In other terms, the formula could be said as "Find the most recent occurrence of Jim in Column A, and count how many occurrences of Jim are in Column B after that point."

Thank you for the assistance.

Try this...

Book1
	A	B	C	D
2	Jim	Bob	_	Jim
3	Bob	harry	_	3
4	Harry	Jane	_	_
5	Bill	Jane	_	_
6	Jim	Bob	_	_
7	Jane	Jim	_	_
8	Bob	Jane	_	_
9	Harry	Jim	_	_
10	Bill	Jane	_	_
11	Jane	Jim	_	_
Sheet2

D2 = count criteria

Formula entered in D3:

=COUNTIF(B11:INDEX(B:B,LOOKUP(2,1/(A2:A11=D2),ROW(A2:A11))),D2)

 

[Peter_SSs]

In Lrec we need to refer to the sheet housing the data of interest, so I have no idea what you mean by
different sheet names.

In the examples I posted I used sheet names of 'Ex A' and 'Ex B'. I was just pointing out that I hadn't forgotten to amend the definition of Lrec to reflect those names instead of 'Sheet1'.