Sumproduct Help

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

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
 
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
 
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
 
Back
Top