Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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)

    Please see attached excel file.

    Thank you!
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    In cell J3 =COUNTIF(C$2:C$11,MAX(C$2:C$11)) then copy across.

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

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

    tadzkie (2016-03-21)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #5
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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...

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tadzkie

    ..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
    Last edited by zeddy; 2016-03-21 at 15:06.

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Last edited by zeddy; 2016-03-23 at 11:45.

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    zeddy (2016-03-23)

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi rory

    Your formula works nicely!
    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. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #11
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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
  •