Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Distinct Result from Multiple Countif
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Distinct Result from Multiple Countif

    all, I need help

    I have 3 columns of data

    store id, store name and item no

    if I create a Pivot by store name and count of item no

    I get 250 store names and counts

    this works

    the problem is I am trying to perform a distinct count of SKUs across 5 stores

    I can do this in a pivot using distinct count and selecting the 5 stores in the Pivot

    the issue I have is that the list I am working from has 5000 rows with 5 stores each

    Can I either do this via a Pivot

    I have tried arrays but it is incredibly slow

    here is an example of the data

    Store Id Store Name item no
    238 St Albans 100034

    397 Watford 100034
    238 St Albans 100103
    397 Watford 100103
    238 St Albans 100269
    238 St Albans 100324
    397 Watford 100324
    238 St Albans 100803
    397 Watford 100803

    I'm basically doing a pivot

    where I am saying

    store 238 has 5 items
    store 397 has 4 items

    what I want is a formula that

    tells me the distinct occurrences across both stores

    so I would define "st albans" and "watford" and the distinct count would be 5

    equally I have to lookup from the text "st albans"

    Can you help please

    R

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,270
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    What array formula did you try? I think the performance with 5K rows would be OK using an array formula to count unique occurrences of numbers like this:
    =SUM(IF(FREQUENCY(....),1))
    Ctrl+Shift+Enter

    M.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,270
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    Something like this


    A
    B
    C
    D
    E
    F
    1
    Store id
    Store Name
    no
    Criteria
    Result
    2
    238
    St Albans
    100034
    St Albans
    5
    3
    397
    Watford
    100034
    Watford
    4
    238
    St Albans
    100103
    5
    397
    Watford
    100103
    6
    238
    St Albans
    100269
    7
    238
    St Albans
    100324
    8
    397
    Watford
    100324
    9
    238
    St Albans
    100803
    10
    397
    Watford
    100803
    11
    444
    xxx
    100034
    12
    555
    yyy
    100034
    13
    444
    xxx
    100103
    14
    555
    yyy
    100103
    15


    Criteria in E2:E3

    Array Formula in F2
    =SUM(IF(FREQUENCY(IF(B2:B30000<>"",IF(ISNUMBER(MATCH(B2:B30000,E2:E3,0)),C2:C30000)),C2:C30000),1))
    Ctrl+Shift+Enter

    Observe i used ranges with almost 30K rows

    M.

  4. #4
    New Member
    Join Date
    Aug 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    I probably havent been clear enough in my original thread

    so the output file looks like this


    AL11 - St Albans - Watford - Watford North - St Albans North - Harrow

    So for each sector I get 5 stores

    Each will have a count of item no

    AL11 - St Albans - Watford - Watford North - St Albans North - Harrow
    5000 4000 4500 5000 3000

    what I need is a distinct count across the 5 stores

    so I am left with AL11 - 5287

    the file with the sectors is 600k of records

    the file with the store id, store name and item no combinations is about 100k lines

    I have tried the example array, and it seems to hang

    its almost as though I need to get the individual counts, and then perform a distinct on the 5 count results

    R
    Last edited by Ravin; Jul 17th, 2019 at 04:31 PM.

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,677
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    Just ensure you've used the "add this data to the Data Model" option when creating your Pivot Table. That will then allow you to use the Distinct Count option for the Field Value Setting.
    You Grand Total will be the Distinct Count for all Filtered Rows. Your example only has the 2 stores. With Hundreds of Stores, filtering may be more difficult even if you take advantage of Slicers.
    I have used a separate Table to Connect to to provide an additional filter option.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,270
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    Sorry, I'm not fully understanding what you need. Actually with 600K rows a complex formula should not be a good solution.

    Maybe if you better describe your data, criteria and desired results, someone can help you with a macro (Power Query?).

    Good luck!

    M.

  7. #7
    New Member
    Join Date
    Aug 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    No thanks Marcelo your solution works, I think its the volume of data that is the issue

    With one sector and your formula I get the desired result

    it maybe I need to use a macro, at the current rate it will take quite a few hours to calculate

    R

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,155
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    A pivot table should be quick, even with 600,000 records.

    Give the data a simple defined name, like TheData. Save the file.
    ALT-D-P to start the pivot table wizard, choosing the external data option at the first step. Follow the wizard to the end choosing the option to edit in MS Query.
    Somehow get just the unique records. There are many ways. Such as via the SQL button editing the text to
    Code:
    SELECT DISTINCT [Store ID], [Store name], [item no]
    FROM TheData
    Exit MS Query & finish the pivot table.

    regards
    Last edited by Fazza; Jul 18th, 2019 at 01:30 AM.
    To receive a better answer, put more work into asking the question.


  9. #9
    New Member
    Join Date
    Aug 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    Hi Fazza

    The problem is I have 2 files, one with the combinations

    store id, store name, and item number

    thats 550k of combinations

    which I can pivot from

    the place I want my data is another sheet

    it is laid out

    Sector - store 1, store 2, store 3, store 4, store 5 - distinct item count

    and this has 10k sectors, with different store combinations

    so to populate this sheet I need to go to the pivot for each sector and define the 5 stores to get the distinct result

    but with 10k of sectors this would take too long

    so I ruled out the pivot

    unless there is a process I am missing where I can link the two

    R

  10. #10
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,155
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Distinct Result from Multiple Countif

    i'm not crystal clear

    it sounds like - the sample data is usable and it is just clarity on the result that is needed
    and the result sounds like a cross tab?

    which I'm thinking can come straight from the pivot table. that is, make a pivot table and there is your resultset. if you need it on another sheet, then copy & paste the values.

    Which makes me think an alternative approach is forget the pivot table and make a query instead. it can give a cross tab result.

    though I'm not clear on exactly what is wanted. now I see sectors and I'm getting less clear on what is what

    I think best to mock up some sample data inputs (maybe what you've given already is OK?) and the corresponding output

    maybe, if it is simple to explain, can you explain any issues with the distinct count pivot table solution I proposed in post #8 ?
    To receive a better answer, put more work into asking the question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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