INDIRECT and ADDRESS

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

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!
 
Try this...

=SUMPRODUCT(--(Sheet1!Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")):INDIRECT(ADDRESS(44,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")))

Hope this helps,

Hutch
 
Thanks!

Tom Hutchins said:
Try this...

=SUMPRODUCT(--(Sheet1!Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")):INDIRECT(ADDRESS(44,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")))

Hope this helps,

Hutch
Click to expand...
 
=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:Y8,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.
 
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:Y8,0)))
 
Back
Top