I've got many items in boxes on a pallet. I would like to know how I can count the unique number of boxes in a pallet.

So, based on the above example, the above pallet (P01) has 5 unique boxes (B01-B05) and pallet (P02) has 2 unique boxes (B01-B02). What kind of formula should I use to achieve the result of 5 for P01 and 2 for P02 in column E? I was thinking of using COUNTIF but it doesn't seem to be applicable here. Any advice/help is greatly appreciated.

CodePudding user response：

If you have Excel 365 you can use the following formulas.

I added some helper columns to achieve the result.

=UNIQUE(tblData[Pallet-box ID]) retrieves the unique Pallet boxes (column E)

Based on that =UNIQUE(LEFT(UNIQUE(tblData[Pallet-box ID]),3)) retrieves the unique pallet no. (column G)

And now we can count the pallet no within the pallet boxes: =COUNTIF(E4#,G4# & "*") (column H)

CodePudding user response：

I'm not a specialist myself, but I have just created following example:

    A  B  C  D  E
 7              a
 8              a
 9              a
10              b
11              b

In a cell, I created the formula =UNIQUE(E7:E11) and next to it the formula =COUNTIF(E7:E11,UNIQUE(E7:E11)), these are the results:

a    3
b    2

So, combining COUNTIF() and UNIQUE() basic Excel functions might help you.

Edit: you might use a helper column, based on =LEFT(B1,LEN(A1)): I had put P01 in cell "A1" and P02-B01 in cell "B1" and the result was P02, the left side of P02-B01, based on the length of P01.