Not sure why you're adding 1 to the MATCH.

I think they're using that to calculate the offset of the range from column
A so that the ADDRESS function will return the correct column number.

In the INDEX version you don't need that offset correction:

=SUMPRODUCT(--(Z9:Z44="CAT"),INDEX(B9:Y44,,MATCH(MAX(B8:Y8),B8:Y 8,0)))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=SUMPRODUCT(--(Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(B8:Y8 ),B8:Y8,0)+1))):INDIRECT(ADDRESS(44,(MATCH(MAX(B8: Y8),B8:Y8,0)+1))))

This will do the same thing:

=SUMPRODUCT(--(Z9:Z44="CAT"),INDEX(B9:Y44,,MATCH(MAX(B8:Y8),B8:Y 8,0)+1))

Not sure why you're adding 1 to the MATCH. By adding 1 to the MATCH, if
the match happens to be in Y8 then you'll get an error since that
reference is outside the indexed range.

--
Biff
Microsoft Excel MVP


"Al" wrote in message
...
Thanks to Tom Hutchins yesterday for this formula.
=SUMPRODUCT(--(Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(B8:Y8 ),B8:Y8,0)+1))):INDIRECT(ADDRESS(44,(MATCH(MAX(B8: Y8),B8:Y8,0)+1))))

I need to move this formula to a different sheet and reference the
original
sheet (Sheet1) How do I add the reference to Sheet1.
Thanks!