easy one - Counting values

[Marcus]

Hi,

Should be an easy one, I have a sheet with 64000 items of kit listed against a couple of hundred different contract numbers. Currently in the format:

contract1 kit1
contract1 kit2
contract1 kit3
contract1 kit4
contract2 kit5
contract2 kit6
contract3 kit7
contract3 kit8
contract3 kit9

i.e. this would be 3 contracts, with 4, 2 and 3 items of kit against them. So the question is, how do I count the contract numbers?

Any help would be much appreciated.
Cheers,
Marcus

 

[lenze]

Try Data>Sub Totals

At each change in contract number, count kits

 

[Aladin Akyurek]

64000 cells... that's a lot.

The best thing to do is to use the morefunc.xll add-in's COUNTDIFF function...

=COUNTDIFF(Range)

where Range houses the contract numbers. One caveat though: The Range should not have formula generated "" (blanks), while empty cells are OK.

Otherwise, array-enter...

=SUM(IF(LEN(Range),1/COUNTIF(Range,Range)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

The above formulas produce a count of distinct contract numbers.