SUMPRODUCT or COUNTIF ?

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.

It could also be SUMPRODUCT of "Jim" in Column B minus all "Jim" in Column B prior to the last occurrence of Jim in Column A.

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.

Using your posted data in A1:B10
and
D1: a name in Col_A and Col_B to match...e.g. Jim

This regular formula returns the Col_B count of that name from the rows below the last instance of that name in Col_A

Please Login or Register  to view this content.

With the sample data, the formula returns: 3

Is that something you can work with?

Can you post a workbook with some sample data and expected results?

Thanks for posting the workbook.
Now...can you annotate each Col_D count to explain how it is derived?

Thanks.

Thanks for the updated, annotated workbook. Now, I really do understand. I attached an edited version of that workbook.

Using your posted workbook, this regular formula begins the counting of Col_B values occurring since the last Col_A instance.

Please Login or Register  to view this content.

Copy that formula down through C17.

I'll use the formula from C10 to explain...
One major assumption here is that the same value will NOT be in the same row of Col_A and Col_B.

C10: =IFERROR(COUNTIF(INDEX(B:B,LOOKUP(2,1/(A$2:A10=B10),ROW(A$2:A10))+1):B10,B10),COUNTIF(B$2:B10,B10))

In this section:
COUNTIF(INDEX(B:B,LOOKUP(2,1/(A$2:A10=B10),ROW(A$2:A10))+1):B10,B10)

The COUNTIF function needs a range for the first argument and we'll build that range using an INDEX function and a cell reference.

We know that the end of that range will be the cell to the left of the formula...B10...so that part is easy.

The beginning of the range is determined by the location of the last Col_A instance of the B10 value (Jane) that is above the current row.

The INDEX function section which, in this case will point to a single cell in Col_B needs the row number of that last "Jane" value in Col_A.

That's where the LOOKUP function comes in:
LOOKUP(2,1/(A$2:A10=B10),ROW(A$2:A10))

One of the nice side-effects (bugs?) of the LOOKUP function is...
If the value to find is larger than any of the lookup values, it matches the last value of the same type (number or text) as the value to be found.

In this section: 1/(A$2:A10=B10)
For each referenced Col_A cell that matches B5, the formula returns 1.
All other non-matches return an error.

Consequently, since we're looking for a 2 in a range of 1's and errors,
the LOOKUP will match the last numeric entry...which corresponds to the last Col_A cell that matches "Jane"...A7 in this case.

The third argument of the LOOKUP function is the value to be returned when a match is found...in our case, we want the row number.

So...In the C10 formula...
our COUNTIF function will reference B7:B10 and count the number of "Jane"s. There are 2 "Jane"s in that range...located at C8 and C10.

I hope that helps.