Sumproduct Help
 

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

Sumproduct Help
 

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(MA X(B8:X8),B8:X8,FALSE))))

HTH,
Bernie
MS Excel MVP


"Al" wrote in message
...
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

Sumproduct Help
 

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

"Al" wrote:

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