Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Unique Locations (Excel 97-SR2)

    I'm trying to count the number of new(unique) locations in a table.. I'm stuck... Can someone assist please?

    The formula I have lashed up is... =COUNT(COUNTIF($C$3:C$9,"=0"),COUNTIF(D$3$9,"=64")) Which is patently wrong!

    Attached spreadsheet... is an example of what I'm trying to do?

    Regards
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Unique Locations (Excel 97-SR2)

    I'm sorry, I don't understand what you mean by "new (unique) locations". Could you try to explain it, and what you expect the result to be in the spreadsheet you attached?

  3. #3
    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 Unique Locations (Excel 97-SR2)

    I second Hans' request and also make a suggestion:
    If you put the formula in C12:
    =COUNTIF(C$3:C$9,$B12)

    You can copy it from C12:E15 without having to do all the editing and creating new formulas for each row.

    As per your formula, it will always yield 2, since you are counting 2 things (numbers):
    COUNTIF($C$3:C$9,"=0") and COUNTIF(D$3$9,"=64") each is a value (no matter what is in the cells) and you told excel to count them.

    Just a guess: are you trying for this ARRAY (confirm with ctrl-alt-enter) in D17 ( count the values of each when col c = 0) copy from D1820

    =SUM(IF((C$3:C$9=0)*(D$3$9=$B17),1))

    And this array for E17 (copy to E18: E20):
    =SUM(IF((C$3:C$9=0)*(D$3$9=0)*(E$3:E$9=$B17),1))

    Count the values in E when both C&D have 0 or perhaps:
    =SUM(IF(((C$3:C$9=0)+(D$3$9=0))*(E$3:E$9=$B17),1))

    Count the values in col B when EITHER C or D is 0


    Steve

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Unique Locations (Excel 97-SR2)

    Hi Hans/Steve

    Apologies for my poor explanation.... Steve manged to understand what I was mumbling about:

    <font color=red>Just a guess: are you trying for this ARRAY (confirm with ctrl-alt-enter) in D17 ( count the values of each when col c = 0) copy from D1820

    =SUM(IF((C$3:C$9=0)*(D$3$9=$B17),1))

    And this array for E17 (copy to E18: E20):
    =SUM(IF((C$3:C$9=0)*(D$3$9=0)*(E$3:E$9=$B17),1))

    Count the values in E when both C&D have 0 <font color=black>

    Updated spreadsheet attached. Essentially what I am trying to do is understand how many NEW locations each additional user brings. Steve's formulas have answered my query.

    Thanks Steve! (and Hans)

    Regards
    Peter

Posting Permissions

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