Sumproduct help
 

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!

Sumproduct help
 

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: G120))

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

XL2002, WinXP


"Sandy" wrote:

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!

Sumproduct help
 

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!

"Ron Coderre" wrote:

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: G120))

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

XL2002, WinXP


"Sandy" wrote:

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!

Sumproduct help
 

Would this work for you?

=SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G 2))*(B3:G120))

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

XL2002, WinXP


"Sandy" wrote:

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!

"Ron Coderre" wrote:

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: G120))

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

XL2002, WinXP


"Sandy" wrote:

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!

Sumproduct help
 

Absolutely!
Thanks Ron

"Ron Coderre" wrote:

Would this work for you?

=SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G 2))*(B3:G120))

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

XL2002, WinXP


"Sandy" wrote:

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!

"Ron Coderre" wrote:

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: G120))

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

XL2002, WinXP


"Sandy" wrote:

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!