+ Reply to Thread
Results 1 to 2 of 2

Counting values based on a criteria with duplicates

  1. #1
    Registered User
    Join Date
    08-29-2018
    Location
    Cleveland, OH
    MS-Off Ver
    MS Office 2010
    Posts
    10

    Counting values based on a criteria with duplicates

    Good Afternoon,

    I am trying to count the number of times a particular store number appears on a spreadsheet for tracking purposes. The only criteria I need met is that I only want stores to be counted if they are of a particular Division. However, there are going to be duplicates, I only want the store to be counted once, even if it shows up multiple times. I have attached a dummy shreadsheet. I have a small table in H3:I6 that I would want the formulas to be used in. Any help on this matter would be appreciated. Our real file is quite large and would take much too long to count the stores manually.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Counting values based on a criteria with duplicates

    I used a trick I learned a long time ago. Since each store ID is unique, there is no need to take into account the possibility of a store being duplicated in more than one division.

    The trick is to use a helper column to divide the store by the number of times it appears. =1/COUNTIF([Store],[@Store]). Then you can sum up the fractions.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return values based on multiple criteria with no duplicates
    By Ksun23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2018, 08:34 PM
  2. Replies: 13
    Last Post: 04-19-2015, 11:51 PM
  3. [SOLVED] Removing Duplicates based on column values and criteria
    By viduran88 in forum Excel General
    Replies: 9
    Last Post: 04-04-2013, 11:37 AM
  4. Search duplicates values based on certain criteria and then align the day to day data
    By manishgupta_g in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-04-2013, 09:59 PM
  5. Counting Unique Values based on several criteria
    By GowHow in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 10:16 PM
  6. Listing Specific Values Based on Criteria (Including Duplicates)
    By Jace in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2008, 02:57 PM
  7. Counting Values Based on Criteria
    By zachharriman in forum Excel General
    Replies: 4
    Last Post: 03-10-2008, 05:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1