Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Jan 25, 2020
Solved

Count Function

Please assist with a formula.   Thanks.
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 26, 2020

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 26, 2020

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 ) ) )

Resources