1. ## Need your help guys. ASAP if possible.

Hi all!

I need your expertise in excel.

What i want to do is to have a formula in Cell J3 wherein it will show the count of groups from Cells B2:B11 that shows Option 1 as the highest (in that row). (i.e. if Groups 1, 2, 3 have the highest percentage for option 1 as compared to others in there respective rows, the result in J3 should be 3)

Thank you!

2. In cell J3 =COUNTIF(C\$2:C\$11,MAX(C\$2:C\$11)) then copy across.

HTH,
Maud

3. Hi Maud

see my version attached.
I think this is what the User is really asking for.
I used a 'helper' column (in column [P] )
..perhaps you could eliminate my helper column?

zeddy

4. ## The Following User Says Thank You to zeddy For This Useful Post:

5. I took the request as, count the number of groups who have the highest value in each option. If groups 1,2, and 3 all have the highest value, then J3 would be 3.

Away from the puter right now but chances are I read it wrong.

Thanks,
Maud

6. Thanks for the responses! zeddy's formula is what I'm looking for. Thanks though Maudibe..

May i ask why use "--(C2: C11= \$P2: P11)"? I'am just curious...

..the "minus minus" part of the formula is just a trick that turns a series of True/False results into a series of 1 and 0 values, which can then be "summed"

zeddy

8. Hi Maud

..at first glance I thought that too, but I realised that for "If groups 1,2, and 3 all have the highest value, then J3 would be 3" would mean that they were all the same value, rather than "highest value in the row".
Hurt my head for a while.
Felt like I'd fallen into an upholstery machine.
(I'm now fully recovered.)

..anyway, I still have high hopes of you eliminating the need for my 'helper' column when you get back in front of your machine which I can't believe you left alone for even a moment.

zeddy

9. Array enter in J3:
=SUM(COUNTIF(C2:C11,SUBTOTAL(4,OFFSET(\$C\$2:\$G\$2,RO W(\$C\$2:\$C\$11)-MIN(ROW(\$C\$2:\$C\$11)),0))))
and copy across.

10. ## The Following User Says Thank You to rory For This Useful Post:

zeddy (2016-03-23)

11. Hi rory

I know that SUBTOTAL(4, uses the MAX function, but I'm still trying to figure out the rest.
It's like I've fallen into that upholstery machine again.

zeddy

12. The OFFSET part returns an array of 1 row ranges. SUBTOTAL then returns the max for each of those, which in turn is passed as the COUNTIF criteria argument.

13. Hi guys!

Thank you for the responses.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•