Counting Unique Values based on a Criteria

[smalik]

Is there a formula that can count unique values in one column based on a criteria in a different column?

In the example below, I only want to count 07SPE-1 only once for each Pol_Dept field

I have 52,000+ records with Pol_Dept. numbered from 1-9 and letters A-G. However, I don't think that matters. Once I figure out the right formula, the rest is easy.

Hopefully, I explained my question clearly.

Sorry, working through my office computer that does not let me allow to have addons without admin rights. Hopefully, the image helps.

 

[Saba Sabaratnam]

You could use helper column to do it.

Enter the following formula in D4 and copy it down

=1/COUNTIFS(C:C,C4,B:B,B4)

Then enter the following formula in G4 and copy it down

=SUMIFS(D:D,B:B,F4)

Kind regards

Saba

 

[smalik]

Awesome... Thank you.

This works. I never thought of the "=1/COUNTIFS(C:C,C4,B:B,B4)" formula.

 

[Saba Sabaratnam]

You are welcome

 

[Dave Patton]

Try Excel 365's Unique functions

Unique.xlsm
	A	B	C	D	E	F
1
2
3		Pol_dept	Cust_rec		Pol_dept	Cust_rec
4		2	07SPE_1		2	2
5		2	07SPE_1		3	1
6		2	1169E_1
7		3	07SPE_1
8		3	07SPE_1
9		3	07SPE_1
10		3	07SPE_1
1b

Cell Formulas
Range	Formula
E4:E5	E4	=SORT(UNIQUE(B4:B10))
F4:F5	F4	=ROWS(UNIQUE(FILTER(C$4:C$10,B$4:B$10=E4)))
Dynamic array formulas.

 

[smalik]

Try Excel 365's Unique functions

Unique.xlsm
	A	B	C	D	E	F
1
2
3		Pol_dept	Cust_rec		Pol_dept	Cust_rec
4		2	07SPE_1		2	2
5		2	07SPE_1		3	1
6		2	1169E_1
7		3	07SPE_1
8		3	07SPE_1
9		3	07SPE_1
10		3	07SPE_1
1b

Cell Formulas
Range	Formula
E4:E5	E4	=SORT(UNIQUE(B4:B10))
F4:F5	F4	=ROWS(UNIQUE(FILTER(C$4:C$10,B$4:B$10=E4)))
Dynamic array formulas.

Is the formula in Column E necessary? Can it be a typed in value?

 

[Dave Patton]

What happened when you tried that?

 

[smalik]

You could use helper column to do it.

Enter the following formula in D4 and copy it down

=1/COUNTIFS(C:C,C4,B:B,B4)

Then enter the following formula in G4 and copy it down

=SUMIFS(D:D,B:B,F4)

Kind regards

Saba


View attachment 40577

This works fine with small data set but when I tried it on my actual data (over 50,000 rows), I get a #Div/0! error. Any idea why?

 

[smalik]

What happened when you tried that?

Last night when I did it, I was getting a circular error message. I did it again this AM and it worked fine. I guess I must be doing something wrong. Thanks.