*My original question was not quite as clear as it might have been, so here's a bit of clarification:*

What I'm working with is a list of items for use in a pivot table. Each row represents an item, and a person can have multiple items, so the same person's name appears on multiple lines. The items are ranked 1-15. For the final output we're looking for a count of individuals per item. We want each person to be counted exactly once, and we want them counted for the highest ranked item they have. In the end this gives us a tally of groups of individuals, the groups being the highest ranked item each person has.

People's names are in Column A and the ranks are in Column F. I had a formula that could find the highest F per A, but it failed when new data was loaded and the total number of rows changed.

*This was my original question, along with the formulas I was working with:*

I have an array formula that works unless the number of entries in the table changes. I know how to find the last row, but I don't know how to combine the two. Here are the details:

I have a table that's pulling data from a dynamically updating query on another sheet. It has duplicate text values in column A and unique numeric values in column F. In column G I have an array formula. If the F value on the current row is the maximum F for the corresponding A, it returns 1, if not it returns "No" (easier to filter and count only numbers elsewhere, don't ask).

```
A    ...     F
Joe         1.2
Joe        12.7
Ralph       3.5
Ralph       4.1
Ralph       0.5
Warren     12.9
Mike        6.4
Mike        8.5
Ralph       1.3
Warren      7.9
Warren     14.8
```

```
{=IF(A2="","",IF(F2=MAX(($A$2:$A$1719=A2)*($F$2:$F$1719)),N(1),"No"))}

```

This formula works fine except that the total range of entries can change on any given day. Today there are 1718 rows, plus column headers. Tomorrow I could have 1300 entries, or 2000 (though never more than 2000). If there are blank lines in column A, the entire formula fails and the rest of the report collapses.

I know I can get the number of the last row in column A with text by using:

```
=MATCH(TRUE, INDEX(ISBLANK('SourceQuery'!A1:A2001), 0, 0), 0)-1

```

Today that formula returns "1719" which is exactly what I want. In the first formula you can see two cell range references involving that row number.

What I can't figure out is how to combine the two. In theory I should just be able to replace the $A$2:$A$1719 reference in the first formula with some sort of reference involving the second formula, but I can't find a way to do this that works. Any ideas?

*For future lost souls trying to solve the same problem as me, there were two good answers to this. You can read the details in the answers below, but here's the quick reference.*

If you want to do this by my original method of using an array formula, use this:
`{=IF(A2="","",IF(F2=MAX((INDIRECT("$A$2:$A$" & (MATCH(TRUE, INDEX(ISBLANK('SourceQuery'!A1:A2001), 0, 0), 0)-1))=A2)*(INDIRECT("$F$2:$F$" & (MATCH(TRUE, INDEX(ISBLANK('SourceQuery'!A1:A2001), 0, 0), 0)-1)))),N(1),"No"))}`

If you don't want to be a crazy person like I was, you don't need to use an array formula at all.
`=IF(A2="","",IF(COUNTIFS(A:A,A2,F:F,">"&F2)>0,"No",1))`