Multi Criteria counting

[davee1]

I have a column of Serial Numbers with many duplicates, each Manufacturer's Serial number falls into a specific range (e.g. Manufacturer A; 0 - 900000, Manufacturer B; 1000000 to 10000000 etc.) I'm trying to come up with a count of unique Serial numbers per manufacturer. I've tried a number of formulas and combinations of formulas including the COUNTDIFF add-in, but can't seem to nail it. Any Ideas? I have over 400000 rows to parse.

thanks

 

[rconverse]

I have a column of Serial Numbers with many duplicates, each Manufacturer's Serial number falls into a specific range (e.g. Manufacturer A; 0 - 900000, Manufacturer B; 1000000 to 10000000 etc.) I'm trying to come up with a count of unique Serial numbers per manufacturer. I've tried a number of formulas and combinations of formulas including the COUNTDIFF add-in, but can't seem to nail it. Any Ideas? I have over 400000 rows to parse.

thanks

Using a pivot table and the grouping feature may work.

 

[stanleydgromjr]

davee1,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[gardnertoo]

If you can add a "helper" column, this solution should work. I've created some dummy data to test it on, see below. The gray column is the helper column, which creates a data point equal to the inverse of the number of times a given serial number appears in the list. This way, when we add them up, that serial number will add up to 1. I highlighted the duplicate serial numbers manually. Obviously, you will have to change the range refered to by the formula. Here's the formula from cell H2 as written:

=SUMPRODUCT(--($B$2:$B$26>=E2),--($B$2:$B$26<=F2),--($C$2:$C$26))

Book1
	A	B	C	D	E	F	G	H
1	Manuf	Serial Num	Inverse Count	Manuf	Lowest Num	Highest Num	raw count	unique count
2	B	35	0.5	Manuf A	0	24	6	4
3	A	10	1	Manuf B	25	49	12	8
4	B	36	0.5	Manuf C	50	74	2	2
5	B	47	0.5	Manuf D	75	99	5	3
6	B	36	0.5
7	D	76	0.5
8	D	76	0.5
9	B	30	1
10	A	3	0.333333333
11	B	35	0.5
12	B	40	1
13	B	47	0.5
14	C	67	1
15	C	68	1
16	A	3	0.333333333
17	D	98	0.5
18	D	98	0.5
19	A	16	1
20	B	38	1
21	B	33	0.5
22	B	26	1
23	A	3	0.333333333
24	D	89	1
25	A	5	1
26	B	33	0.5
Sheet1