Distinct Result from Multiple Countif

Ravin

New Member
Joined
Aug 24, 2012
Messages
44
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Ravin

New Member
Joined
Aug 24, 2012
Messages
44
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:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707

ADVERTISEMENT

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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Ravin

New Member
Joined
Aug 24, 2012
Messages
44

ADVERTISEMENT

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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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:

Ravin

New Member
Joined
Aug 24, 2012
Messages
44
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,732
Messages
5,597,798
Members
414,176
Latest member
LK88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top