Sumproduct Help

[Al]

Hello
I am trying to total values in range B9:Y44 in the Column where the max of
B8:Y8 is found and where Range Z9:Z44 ="CAT"

Thanks

 

[Bernie Deitrick]

Al,

=SUMIF(Z9:Z44,"CAT",INDEX(B9:X44,,MATCH(MAX(B8:X8),B8:X8,FALSE)))

Or using sumproduct:

=SUMPRODUCT((Z9:Z44="CAT")*(INDEX(B9:X44,,MATCH(MAX(B8:X8),B8:X8,FALSE))))

HTH,
Bernie
MS Excel MVP

 

[Tom Hutchins]

This works, but there must be a better way:

=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))))

Hope this helps,

Hutch