Sumproduct help

[Guest]

Repost for clarification and hopefully a quicker answer....
Sorry for the repost I tried to calrify yesterday but probably better to
start all over

My data is in A1:G12 I need a formula that will find the max of B2:G2, then
in that column count the cells >0 where the value in ColA is DOG.

something like countif(max(b2:G2),A:A="DOG")

I believe it will take sumproduct to accomplish but I can not figure it out.

Thanks!

 

[Guest]

Try something like this:

Assuming Row_1 contains headings
and the actual data is in A2:G12

This formula returns the count of cell values >0
where the corresponding Col_A value is "DOG"
in the column containing the max Row_2 value
=SUMPRODUCT((A3:A12="DOG")*(B2:G2=MAX(B2:G2))*(B3:G12>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

This is close. ColA contains DOG1 DOG2, DOG3 etc. Is the only way to catch
all of the dogs LOL to modify by adding iterations of the original formula
and inserting DOG1, DOG2,DOG3 etc since apprently wildcards do not work in a
SUMPRODUCT formula.
Thanks!

 

[Guest]

Would this work for you?

=SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G2))*(B3:G12>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Absolutely!
Thanks Ron

Would this work for you?

=SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G2))*(B3:G12>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP