Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count only once (2000)

    Hi All, I need your help again.
    I have a spreadsheet with the names of guest from A2:A5000. In B2:B5000 are numbered boxes that contain their personal items. What we are trying to do is figure out how many boxes we have used this year. The problem is that one box might contain up to 5 different guest items. Example A3 John has items in box 15 but so does A500 Mary and A800 Mike. How can we get excel to only count box 15 once?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count only once (2000)

    This can be done by using array formulas.
    See Count Unique Elements in a Cell Range in Excel from the MS knowledge base for some examples.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count only once (2000)

    If the unique box count is not conditioned on the names associated with boxes:

    =SUM(IF(B2:B7<>"",1/COUNTIF(B2:B7,B2:B7))

    which must be confirmed with control+shift+enter instead of just with enter. The formula is an extended version of the original due D. Hager.

    What follows is an equivalent of the above with SumProduct, due to H. Grove...

    =SUMPRODUCT((B2:B7<>"")/COUNTIF(B2:B7,B2:B7&"")

    Needs just enter.
    Microsoft MVP - Excel

Posting Permissions

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