Multi Criteria counting

davee1

New Member
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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.

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
ABCDEFGH
1ManufSerial NumInverse CountManufLowest NumHighest Numraw countunique count
2B350.5Manuf A02464
3A101Manuf B2549128
4B360.5Manuf C507422
5B470.5Manuf D759953
6B360.5
7D760.5
8D760.5
9B301
10A30.333333333
11B350.5
12B401
13B470.5
14C671
15C681
16A30.333333333
17D980.5
18D980.5
19A161
20B381
21B330.5
22B261
23A30.333333333
24D891
25A51
26B330.5
Sheet1

Replies
1
Views
438
Replies
4
Views
513
Replies
8
Views
606
Replies
1
Views
241
Replies
20
Views
583

1,203,062
Messages
6,053,309
Members
444,652
Latest member
ibcap

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.

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

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