How do I set up this count?

[kmprice710]

I have a list of customers

Column 1: Account numbers
Column 2: Different departments attached to the account number.
Column 3: Supplier Shipment Numbers

I need to count how many times each specific department is listed under the same supplier shipment number. But I can't count departments without also including the Account number because supplier shipments sometimes include multiple accounts. So I dont want to accidentally count Finance Dept. for Account #101 appearances under transaction # 15 and include Finance Dept. for Account #201 appearances under transaction #15 but those Finance departments are completely separate departments.

How do I set up the count?

 

[kmprice710]

When I type that into D2, I get an error that says "That function isn't valid"

Sorry but I am still stumped. I tried to use a smaller sheet and still get that function invalid error.

 

[Fluff]

Are you using xl 365 as your profile shows?

 

[kmprice710]

Are you using xl 365 as your profile shows?

My work computer uses Professional Plus 2019

 

[Fluff]

In that case I would suggest you change your profile to show that.

I'm not sure how to get round the 16 digit issue with 2019

 

[kmprice710]

In that case I would suggest you change your profile to show that.

I'm not sure how to get round the 16 digit issue with 2019

Often I am at home using my home computer on which I have 365. Should I change my profile every time that I make a post?

 

[Fluff]

No need to change your profile, but clearly state state which version a solution needs to work with.

 

[kmprice710]

No need to change your profile, but clearly state state which version a solution needs to work with.

Agreed, I didnt know the digit issue existed either.

Could I solve this by cutting the spreadsheet into smaller bits and then recombining? I have nearly 700000 rows and the first solution that was suggested took 4 hours to reach 5% complete. Maybe I could get the second solution to work on my home computer running 365 if I cut the sheets down? My home computer is a LOT slower than my work one so there is a tradeoff.

 

[Fluff]

The countifs solution will not work on your data as it will only see the 1st 15 digits.
As you can see rows 2 & 11 are being counted as the same value

Fluff.xlsm
	A	B	C	D	E
1	Supplier delivery	Account	Department	Count
2	1115199600840152	84	077kingk01	1	2
3	1115199600840152	84	077hamir01	1	1
4	1115199600840150	84	077voskj01	1	1
5	1115199600840152	84	077joner01	1	1
6	1115199600840152	84	077moorr01	1	1
7	1115199600840152	84	077inyar01	1	1
8	1115199600840152	84	077freek01	1	1
9	1115199600840152	84	077klaia01	1	1
10	1115199600840152	84	077carsd01	1	1
11	1115199600840150	84	077kingk01	1	2
12	1115199600840152	84	077hardm01	1	1
13	1115199600840152	152	141reedn01	1	1
14	1115199600840152	152	141patta01	1	1
15	1115199600840152	152	999collj02	1	1
16	1115199600840152	152	141lewim01	1	1
17	1115199600840152	152	141guyta01	1	1
18	1115199600840152	152	141millc01	1	1
19	1115199600840152	152	141jimel01	1	1
20	1115199600840152	152	141mandr01	1	1
21	1115199600840152	152	141egger01	1	1
22	1115199600840152	152	141mujeh01	1	1
Lists

Cell Formulas
Range	Formula
D2:D22	D2	=ROWS(FILTER($A$2:$A$100,($A$2:$A$100=A2)*($B$2:$B$100=B2)*($C$2:$C$100=C2)))
E2:E22	E2	=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)

 

[kmprice710]

The countifs solution will not work on your data as it will only see the 1st 15 digits.
As you can see rows 2 & 11 are being counted as the same value

Fluff.xlsm
	A	B	C	D	E
1	Supplier delivery	Account	Department	Count
2	1115199600840152	84	077kingk01	1	2
3	1115199600840152	84	077hamir01	1	1
4	1115199600840150	84	077voskj01	1	1
5	1115199600840152	84	077joner01	1	1
6	1115199600840152	84	077moorr01	1	1
7	1115199600840152	84	077inyar01	1	1
8	1115199600840152	84	077freek01	1	1
9	1115199600840152	84	077klaia01	1	1
10	1115199600840152	84	077carsd01	1	1
11	1115199600840150	84	077kingk01	1	2
12	1115199600840152	84	077hardm01	1	1
13	1115199600840152	152	141reedn01	1	1
14	1115199600840152	152	141patta01	1	1
15	1115199600840152	152	999collj02	1	1
16	1115199600840152	152	141lewim01	1	1
17	1115199600840152	152	141guyta01	1	1
18	1115199600840152	152	141millc01	1	1
19	1115199600840152	152	141jimel01	1	1
20	1115199600840152	152	141mandr01	1	1
21	1115199600840152	152	141egger01	1	1
22	1115199600840152	152	141mujeh01	1	1
Lists

Cell Formulas
Range	Formula
D2:D22	D2	=ROWS(FILTER($A$2:$A$100,($A$2:$A$100=A2)*($B$2:$B$100=B2)*($C$2:$C$100=C2)))
E2:E22	E2	=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)

But I could download the file on my home computer, open in 365 and do the rows/filter solution?

 

[Fluff]

The formula I supplied will work with 365.