Forum Discussion
Count Function
Please assist with a formula.
Thanks.
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.
5 Replies
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 ) ) )
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.
- Thank you Riny. It works to my expectation!
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-excel/
