INDIRECT and ADDRESS
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!
|