+ Reply to Thread
Results 1 to 7 of 7

Can't "Countif" data duplicated only against one criteria rather than across entire range

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Can't "Countif" data duplicated only against one criteria rather than across entire range

    Attached file 160202 Ignore duplicate per client.xlsx has three columns, listing Clients, Types of Jobs, and Colours.

    For each client, I need to establish how many different TYPES of jobs (not the total of jobs themselves) were carried out where the colour was Orange.

    I can "countif" a Job Type occurs more than once in the entire Data Range., but can't work out how to link the "countif" to a particular client?

    Problem is the same job can be listed against more than one client. It should only be ignored as a "duplicate" for a client if it was done twice for them.

    So I need the formula to select only the Jobs relating to a client, then ignore any duplicates within that client "set".

    Second stage is then to count only the individual job types performed for that client if they were Orange.

    Hope someone can point me in the right direction!

    Ochimus

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    Hi
    Use the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where $G10:G$12 are the Client, $H$7 is "Orange"

    See the file 160202 Ignore duplicate per client.xlsx
    Regards

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Client
    Job
    Colour
    2
    Client 3
    Job 1
    Orange
    3
    Client 4
    Job 2
    Red
    4
    Client 2
    Job 3
    Yellow
    5
    Client 4
    Job 4
    Yellow
    6
    Client 4
    Job 1
    Red
    7
    Client 4
    Job 2
    Red
    Colour:
    Orange
    8
    Client 2
    Job 3
    Blue
    Answer should be:
    9
    Client 4
    Job 4
    Orange
    Client
    Job Types
    10
    Client 4
    Job 1
    Red
    Client 2
    1
    (Job 2)
    11
    Client 3
    Job 2
    Blue
    Client 3
    1
    (Job 1)
    12
    Client 4
    Job 3
    Yellow
    Client 4
    1
    (Job 4)
    13
    Client 2
    Job 4
    Red
    14
    Client 3
    Job 1
    Red
    15
    Client 2
    Job 2
    Orange
    16
    Client 2
    Job 3
    Green
    17
    Client 4
    Job 4
    Yellow


    This array formula** entered in H10 and copied down:

    =SUM(IF(FREQUENCY(IF((A$2:A$17=G10)*(C$2:C$17=H$7),MATCH(B$2:B$17,B$2:B$17,0)),ROW(B$2:B$17)-ROW(B$2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    Many thanks to both for prompt response and solutions.

    Tony, sorry to sound obtuse, but can you run me through the second half of the formula, where it seems to be matching against itself with an interesting row element?

    Ochimus

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    Here's an explanation I wrote for a very similar question.

    The formula is slightly different but the FREQUENCY MATCH portion does the exact same thing...

    Let's use this smaller data sample to see how this works:

    Bill..........Yo-Yo......5
    Charlie.....Guitar....50
    Fred........Yo-Yo......5
    Efrem......Pomade...6
    Bill..........Yo-Yo......5

    E2 = Yo-Yo

    Let's use just this portion of the formula:

    COUNT(1/FREQUENCY(IF(B2:B6=E2,MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-MIN(ROW(A2:A6))+1))

    Everything is dependent upon the product being a Yo-Yo. So we use the conditional IF and MATCH to determine what our data_array will be for use in the FREQUENCY function. We use the ROW()-MIN(ROW())+1 expression to determine what the bins will be for use in the FREQUENCY function

    With the conditional test, IF(B2:B9=E2 (product = Yo-Yo), we get an array of TRUE or FALSE.

    B2 = E2 = T
    B3 = E2 = F
    B4 = E2 = T
    B5 = E2 = F
    B6 = E2 = T

    Where this condition is TRUE we use MATCH to generate an array of values that we will then use to get the count of the frequencies from.

    MATCH returns the relative position of the lookup value within the lookup array. Also, MATCH will find only the first instance of the lookup value. The lookup values are the customer names and the lookup array is also the customer names. Where the conditional IF is FALSE the MATCH function will also return FALSE. So, this is the array generated by the MATCH function:

    MATCH(A2,B2:B6,0) = 1
    MATCH(A3,B2:B6,0) = F
    MATCH(A4,B2:B6,0) = 3
    MATCH(A5,B2:B6,0) = F
    MATCH(A6,B2:B6,0) = 1

    Notice how there are 2 instances of 1. This is because both A2 and A6 contain Bill and as I mentioned MATCH will only find the first instance of the lookup value.

    This is the data array used in the FREQUENCY function:

    {1,F,3,F,1}

    Now we need to generate the bins array.

    Since MATCH returns *relative* positions the bins array needs to be an array of numbers that correspond to these relative postions. Based on this sample data the only possible *numbers* that can be returned by MATCH are 1,2,3,4,5 (or the logical FALSE). So, the bins array must also contain these numbers. We use this expression to get them:

    ROW(A2:A6)-MIN(ROW(A2:A6))+1

    Here's how we get the bins numbers:

    ROW(A2)-MIN(ROW(A2))+1 = 1
    ROW(A3)-MIN(ROW(A2))+1 = 2
    ROW(A4)-MIN(ROW(A2))+1 = 3
    ROW(A5)-MIN(ROW(A2))+1 = 4
    ROW(A6)-MIN(ROW(A2))+1 = 5

    So, we now have a bins array that looks like this:

    {1,2,3,4,5}

    At this point this is what the FREQUENCY function looks like:

    FREQUENCY{1,F,3,F,1},{1,2,3,4,5}

    FREQUENCY ignores logicals so these are the frequencies that are calculated:

    {2,0,1,0,0,0}

    Two 1s and one 3.

    Now we're getting pretty close to the end result!

    The result of the FREQUENCY function is passed to the COUNT function. However, COUNT counts all numbers but we don't want it to count the 0s. We only want the count of numbers that are >0. Here's how we do that:

    COUNT(1/{2,0,1,0,0,0})

    1 / 2 = 0.5
    1 / 0 = #DIV/0!
    1 / 1 = 1
    1 / 0 = #DIV/0!
    1 / 0 = #DIV/0!
    1 / 0 = #DIV/0!

    So, now the COUNT function looks like this:

    COUNT(0.5,DIV,1,DIV,DIV,DIV)

    So:

    COUNT = 2

    Meaning, there are 2 unique customers that bought Yo-Yo's!

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    Tony,

    You are a gentleman.

    Fortunately the BBC have almost finished showing War and Peace, saving me the trouble of re-reading it, because I need clearly to get to grips in depth with the more "advanced" merged concepts like this example that Bill's boys build into Excel!

    Ochimus

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Can't "Countif" data duplicated only against one criteria rather than across entire ra

    You're welcome. Thanks for the feedback!

+ 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. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  2. Replies: 3
    Last Post: 11-11-2015, 03:44 PM
  3. Replies: 0
    Last Post: 11-22-2013, 01:36 PM
  4. Applying a "Left" formula to an entire column with heading "ValueCol"
    By aad401 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 04:14 PM
  5. Performing "SUMIF" where "range" & criteria" are texts
    By joseedua in forum Excel General
    Replies: 1
    Last Post: 02-11-2010, 01:09 PM
  6. Why does =countif(range,"<>""") count empty cells?
    By Ingeniero1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2008, 01:40 PM
  7. [SOLVED] "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du
    By TC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-11-2005, 10:06 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