Formula to List of Product Groups That Have The Most Products With Zero Sales

**chaddug** · 04-17-2015, 03:04 PM

I need a formula that will look at a query and tell me which product groups have the most products with zero sales (Top 5).

As a very simple example, if Product Group1 has 10 products, 3 of which have zero sales, Product Group2 has 15 products and 5 of them have zero sales, the formula would return:
1) Product Group2
2) Product Group1.

If that doesn't make sense I will attach an example workbook with what I want. I imagine this will have to be an array formula, but unfortunately I really don't understand how those work. If possible I would like to not use a countif or countifs because they slow down the workbook so much, but if they need to be used, that is fine.

Thanks in advance for any help, and please let me know if you need clarification.

Example Workbook:
example book.xlsx

**etaf** · 04-17-2015, 03:36 PM

would a pivot table work for you

you can sort by count of sales and filter on zero

then you can just update/refresh the pivot table each time you enter new data

see attached

OR

you could use a formula
for all the product groups
=COUNTIFS(I:I,B2,K:K,0)

and then sort by the count