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?

 

[Herakles]

Using COUNTIFS include all three columns in the formula.

 

[kmprice710]

Thanks but those counts arent quite right. Finance 101 should be 2. Finance 201 is a different entity.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kmprice710]

I got the xls2bb to download and install but the capture section is grayed out and apparently disabled?

 

[Marcílio_Lobão]

kmprice710, good morning.

Herakles' formula works perfectly.

For some strange reason in his example the result is wrong.

Try using it with your data as your example and the formula will give you the result you expect.

I hope I helped with this comment.

I wish you both a great day.

 

[Fluff]

the capture section is grayed out and apparently disabled

Have a look here Xl2bb is disabled

 

[kmprice710]

It did not seem to give me the correct counts. The counts for these should be all 1 except for one of the values in column C

EDIT: Apparently the counts were not wrong from the formula but rather the formula did not completely finish because there are 698,000 rows in this spreadsheet. It says 16 threads 1% after 20 minutes. I don't think this solution will work because of the number of rows?

Supplier delivery	Account	Department	Count
1115199600840152	84	077kingk01
1115199600840152	84	077hamir01
1115199600840152	84	077voskj01
1115199600840152	84	077joner01
1115199600840152	84	077moorr01
1115199600840152	84	077inyar01
1115199600840152	84	077freek01
1115199600840152	84	077klaia01
1115199600840152	84	077carsd01
1115199600840152	84	077kingk01
1115199600840152	84	077hardm01
1115199600840152	152	141reedn01
1115199600840152	152	141patta01
1115199600840152	152	999collj02
1115199600840152	152	141lewim01
1115199600840152	152	141guyta01
1115199600840152	152	141millc01
1115199600840152	152	141jimel01
1115199600840152	152	141mandr01
1115199600840152	152	141egger01
1115199600840152	152	141mujeh01

 

[Fluff]

The problem is that your supplier delivery numbers are more than 15 digits.
Try

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

 

[kmprice710]

The problem is that your supplier delivery numbers are more than 15 digits.
Try

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

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