Count Function

[A_SIRAT]

Please assist with a formula.

Thanks.

[#2]

@A_SIRAT 

CountIF

Using your example:

In column F next to each name you could use: 

As a standard formula: =sumproduct(1/CountIf(range,range) 

For example:

Pearl          =SUMPRODUCT(1/COUNTIF(B6:B10,B6:B10))

Horntree    =SUMPRODUCT(1/COUNTIF(B11:B11,B11:B11))

Red Sky      =SUMPRODUCT(1/COUNTIF(B12:B18,B12:B18))

Miranda      =SUMPRODUCT(1/COUNTIF(B19:B25,B19:B25))

Mexicana     =SUMPRODUCT(1/COUNTIF(B22:B22,B22:B22))

Parva           =SUMPRODUCT(1/COUNTIF(B27:B31,B27:B31))

As an array =sum(1/CountIf(range,range))

(remember after typing the below formula hit CTRL+SHIFT+ENTER, this will add curly brackets before and after automatically. 

For example:

Pearl          =sum(1/COUNTIF(B6:B10,B6:B10))

Horntree    =sum(1/COUNTIF(B11:B11,B11:B11))

Red Sky      =sum(1/COUNTIF(B12:B18,B12:B18))

Miranda      =sum(1/COUNTIF(B19:B25,B19:B25))

Mexicana     =sum(1/COUNTIF(B22:B22,B22:B22))

Parva           =sum(1/COUNTIF(B27:B31,B27:B31))

each of the above will look like the following after hitting CTRL+SHIFT+ENTER

Pearl          {=sum(1/COUNTIF(B6:B10,B6:B10))}

Horntree    {=sum(1/COUNTIF(B11:B11,B11:B11))}

Red Sky      {=sum(1/COUNTIF(B12:B18,B12:B18))}

Miranda      {=sum(1/COUNTIF(B19:B25,B19:B25))}

Mexicana     {=sum(1/COUNTIF(B22:B22,B22:B22))}

Parva            {=sum(1/COUNTIF(B27:B31,B27:B31))}

For more information and examples: 

https://www.ablebits.com/office-addins-blog/2016/04/07/how-to-count-distinct-and-unique-values-in-ex...

[#3 · Accepted solution]

@A_SIRAT

Alternatively in F5 and copy down.

=SUMPRODUCT(IFERROR(1/(E5=$A$2:$A$33),""),1/COUNTIF($B$2:$B$33,$B$2:$B$33))

Depending on your Excel version you may have to press Ctrl-Shift-Return to enter it. Note that your list doesn't have to be sorted by commodity. It will always count the number of boxes that each commodity has in storage.

[#4]

@david kay 

The formula you have just suggested works but I have to manually select the specific range of the commodity on the left i.e. in column E.

[#5]

Thank you Riny. It works to my expectation!

[#6]

@A_SIRAT 

For the record, an Office 365 solution:

The total boxes is given by

= ROWS( UNIQUE( data ) )

whilst the number for each given commodity is

=  ROWS( UNIQUE( FILTER( data, Commodity=@DistinctCommodity ) ) )