Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count if and (XP)

    It's me again. See attached - now she wants a formula in A5 on the "Totals" sheet that looks at Column B, Sheet 1 AND IF a 9 is found, AND IF what's in the same row as the 9 in Column H is a "yes", then count the number of 9's that have a "yes" . ?????? Clear as mud - or not.

    It's the best I can do - help please.

    thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count if and (XP)

    Try this.
    I made an ARRAY formula. confirm with ctrl-shift-enter not enter

    I added some "yes" to sheet1 just for demo.
    I also took the liberty of changing the Totals! A2:E2 to the numbers 9 - 12 (I custom formatted to 0" Grade" to keep the display as 9 Grade - 12 Grade). Putting the numbers made being able to copy fomulas across the columns

    Steve
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if and (XP)

    Thanks Steve - perfect. I am intersted to know why the "UPPER" in the formula. I thought all that function did was convert text to upper case and I don't understand why that part is needed in this formula. Please enlighten me - I'm always anxious to learn.

    Linda

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if and (XP)

    Try using =SUM((Sheet1!$C$3:$C$1492=A$2)*(Sheet1!$H$3:$H$149 2="YES")) instead of
    =COUNT(IF((UPPER(Sheet1!$H$3:$H$1492)="YES")*(Shee t1!$C$3:$C$1492=A$2),1)) in cell A5 Sheet!Total

    The end result is the same value and just another way of looking at things.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if and (XP)

    Cool - still would like to know what UPPER did in the first formula you wrote, if you think I can grasp it (ya never know!).

    Regardless - thank you very much for your help - it was a huge help.

    Linda

  6. #6
    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

    Re: Count if and (XP)

    Hi Linda,
    The UPPER function is used to remove any case-sensitivity, since UPPER("yes") = UPPER("Yes") = UPPER("yES") = UPPER("YES") etc so it will count any variation of Yes. In fact this should not be necessary in this case, but it's better to be safe than sorry!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count if and (XP)

    Yes, This was my reasoning. You asked for "YES" but i tend to be lazy and would type "yes" instead which doesn't equal "YES" unless you UPPER it first. Just somethnig to make it a little more user-friendly without adding too much code.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if and (XP)

    And excellent reasoning it was. Thanks for the explanation - anything new I can learn makes me happy.

    Linda

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if and (XP)

    Hi Rory - thank you for the explanation - I'll never forget it now, that's for sure. It's been a pleasure.

    Linda

Posting Permissions

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