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!